Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |