Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello
I'm currently working on a tool to calculate different pieces of information for customer sales with one customer slicer. I want to calculate the percentage of a specific customer's total orders compared to the grand total of all customer's orders. However, if I use the slicer to select the specific customer, the grand total is automaticly changed to that sole customer as well, consequently, the formula always renders 100%.
Say i have the following:
Customer A 20 kg
Customer B 25 kg
Customer C 15 kg
when I put the slicer on 'Customer A', the calculation gives me 20/20 --> 100%
I need it to give me 20/60 --> 33,33%
Is there any way to work around the slicer selection?
Thanks!
Solved! Go to Solution.
Hi,
Your measure should look like this
=SUM(Data[Sold])/CALCULATE(SUM(Data[Sold]),ALL(Data[Customer]))
Hope this helps.
Hi,
Your measure should look like this
=SUM(Data[Sold])/CALCULATE(SUM(Data[Sold]),ALL(Data[Customer]))
Hope this helps.
Thanks! this did the trick for the first part!
I need to exclude 1 particular customer ( in a data set of 50 customers, I need to exclude 1 from the total count.), If I add a second filter with ALLEXCEPT, I still need to create a column that filters out that one customer. I tried adding a FILTER on the ALLEXCEPT as shown below, but it doesn't wat to filter out the customer's name. Any other way to filter this?
=SUM(Data[Sold])/CALCULATE(SUM(Data[Sold]),ALL(Data[Customer]),ALLEXCEPT(FILTER(Data[Customer],"Customer A"))
Thanks!
Hi,
Try this
=SUM(Data[Sold])/CALCULATE(SUM(Data[Sold]),FILTER(ALL(Data[Customer])))
or
=SUM(Data[Sold])/CALCULATE(SUM(Data[Sold]),ALLSELECTED(Data[Customer]))
Create a new column as shown below
Column = DIVIDE(Customer[Value],SUM(Customer[Value])). Select the new column and go to modelling and select Format: Percentage.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |