The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I have a data set that represents people and their characteristics. For this explantion we'll use color, height, handsomeness, IQ, and age.
I can place a card in my report and display the total count of records in the dataset on the card.
I have 5 slicers and I can check the boxes on the slicers to display a total count of the persons meeting the various criteria selected.
I have a second card. What I want on the second card is the percentage of the overall total the displayed number on the first card represents.
So, say I have 200 total people in the dataset. Let's say 120 of them blue and 80 green. Of the green people 50 are tall and 30 are short. Of the short green people, 20 are handsome and 10 are ugly. I can click the slicers for green, short, and ugly to display 10 as a count on the first card. I would also have slicers for IQ and age.
I want the second card to display 5%. Right now it always displays 100%. The 10 green, short, ugly people represent 5% (10/200) of the total people. If I were to manipulate the slicers to have the first card display the count of tall, smart people, as an example, I would want the second card to display the percentage which tall smart people represent of the 200 whole.
I don't know how to connect/filter/adjust/change things to get that interaction.
If this isn't a clear enough explantion, I can try to create a dummy PBIX or tables or something and post it.
Solved! Go to Solution.
Hi @Glu ,
You need to create 2 measures (1 measure for card 1 and 1 for card 2)
Measure 1 will be the total_count = Count(Columnname)
Measure 2 will be % of total_count =
% of total_count =
var _Total = CALCULATE([total_count], ALL(Tablename))
RETURN
DIVIDE([total_count], _Total)
Later put these 2 measures in each card.
Please hit the thumbs up and mark it as a solution if it works for you. Thanks.
@Glu -> Glad that it worked.
Explanation for measure 2:
% of total_count =
var _Total = CALCULATE([total_count], ALL(Tablename)) //create a variable which counts the entries in a particular column... ALL(tablename) here -Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied
RETURN
DIVIDE([total_count], _Total) //here total_count is the numerator(i.e.-count of your column with the filter/slicer selected) and _Total is the measure that I explained above(i.e.- count of everything irrespective of the filters applied)
Hi @Glu ,
You need to create 2 measures (1 measure for card 1 and 1 for card 2)
Measure 1 will be the total_count = Count(Columnname)
Measure 2 will be % of total_count =
% of total_count =
var _Total = CALCULATE([total_count], ALL(Tablename))
RETURN
DIVIDE([total_count], _Total)
Later put these 2 measures in each card.
Please hit the thumbs up and mark it as a solution if it works for you. Thanks.
This appears to have done the trick- thank you. The thing I seem to not have been getting is that I wasn't using a measure on Card 1. It was just showing count(data element). I just dragged the data element over to the field box on the visualization tab and selected count. So I had no way to reference it. Making a measure that was total_count = Count('table'[dataelement]) then lets me reference it and use it with a second measure on a second card. I don't fully understand the DAX on the second card but it works!
Have you tried:
% of total =
VAR _Total = CALCULATE([your count measure], ALL(Table))
RETURN
DIVIDE([Your count measure], _Total)
Proud to be a Super User!
Paul on Linkedin.
I have not tried that. I don't even know what to do with that or where to put it. Is that DAX for a measure that I would then show on Card 2?
I think that is dividing your_count_measure into a total. I don't know how to capture the your_count_measure figure. The number I want is the number that shows up on Card 1 when the user selected filters are applied. I don't know how to "move that over" to act as the numerator for a calculated percentage of the overall total on Card 2.
Correct. The measure is for the second card.
what are you using for the calculation in the Card 1 visual?
Proud to be a Super User!
Paul on Linkedin.
I wasn't using a measure on the first card, but you got me thinking that way. So thank you. i would give you partial marks if I could.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
97 | |
80 | |
62 | |
57 |
User | Count |
---|---|
264 | |
120 | |
115 | |
83 | |
70 |