I've created a banding table that groups order value based on what is selected in a metric.
I want to be able to include the number of customers and the number of orders that make up the total order value in each of the groups eg (data not representative of the attached model)
Band Value # Customers # Orders
<1000 3500 2 5
<3500 7500 1 1
I've tried lots of different ways but the # customer and # orders will not filter based on the time frame.
This is a non-standard calendar
I've attached a power bi model
PBI File attached
Hi Alison,
The Table filters for me when I click on a column in the chart.
Mynda
the # customers and # orders is not calculating for each group banding - that's the issue.
I've tried all sorts - if I get the banding to work for customers and orders then the filter for periods doesn't work
the example model just has the basic workings - it doesn't work as I want it to
Can you please give me an example of what you expect and what it's returning based on the file you provided so we can focus on one example to troubleshoot?
Thanks,
Mynda
File attached with all the measures included. the # Customers by Banding is the one that appears not to be working.
It looks like it is evaluating the total for the customer for the period and then putting it into a band rather than evaluating how many different customers make up the Order Banding column ie total of orders that are within the banding limits
Thanks
Thanks - changing this from [selected # Customer Type metric] to simply [# Customers] or removing the measure completely and adding DISTINCTCOUNT of CUST.ID doesn't give me the correct answers. It still isn't working
In the case of Period 2 FY 2018 ALL Orders there are 3 different customer id's
In the case of Period 3 FY 2018 ALL Orders there are 3 different customer id's
Any ideas?
Can someone help please?
Hi Alison,
I've moved this thread to the open Power Pivot forum as this is outside the scope of the course and I don't currently have time to understand the complexities of your scenario. Hopefully someone else can take a look for you now that it's open to all forum members.
Mynda
Hi Alison,
You have added fields from Banding table (Label) into the pivot table, but there is no relationship between Banding and Orders Intake tables.
Add a calculated column to Orders Intake table, to calculate where the Order Value fits: into <1000, <3500, and so on. Next, add a relationship between the Orders Intake-(new calculated column) and Banding table-Label column. Only then the pivot will be able to understand what you want and it will display the report as you expect.
