Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I am trying to create a bar chart with a percentage of the total number that is bigger than the total count of all categories (because I have a blank value).
I tried using the DIVIDE function but because I don't want to include the blank value in my barchart it doesn't work. What is the trick?
I need to filter AFTER the calculation. But as soon as I filter, the calculation changes.
This is the data in a table
Then I can make this barchart
But I don't want to show BLANK. So if I filter it out, it looks like this
Which is incorrect.
DAX:
N (in table) = DISTINCTCOUNT(Table[Column])
Total (in table) = CALCULATE (DISTINCTCOUNT(Table[Column]);
ALLSELECTED(Table[TypeSecondPlacement]))
% (in table) = DISTINCTCOUNT(Table[Column]) / [Total]
Solved! Go to Solution.
Hi @Anonymous,
Please try the function ALL rather than ALLSELECTED.
Total (in table) = CALCULATE ( DISTINCTCOUNT ( Table[Column] ); ALL ( Table[TypeSecondPlacement] ) )
Best Regards,
Dale
Hi,
In the visual filter section, apply a non blanks condition on the TypeSecondPlacement field.
Hi @Anonymous
Your problem is that PBI doesn't understand that you total is in fact 61 and not 39. When he sum all your columns he have 2 + 22 + 4 + 11 = 39
So 22/39 = 0.56 and this is correct.
If you want to include your 61 as total you need to add a measure = 61 or something like that ...
On your first chart it's working because you are displaying the percentage column with the TypeSecondPlacement, so PBI don't make calculations.
Hope this helped you understand
- Quentin
Hi @quentin_vigne,
Yes, that is exactly my challenge! Thanks for summarizing it so clearly.
Still looking for a solution though!
My challenge is that I also have A LOT of filters, so the 61 also changes with the filters. I cannot just make a 61-measure 😞
Hi @Anonymous,
Please try the function ALL rather than ALLSELECTED.
Total (in table) = CALCULATE ( DISTINCTCOUNT ( Table[Column] ); ALL ( Table[TypeSecondPlacement] ) )
Best Regards,
Dale
@Anonymous
Can you provide us a sample of data ?
- Quentin
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |