Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I put together the following table as an example of my data table:
Year | Gender | Score(1-4) | Range | Percentage |
1 | M | 3 | 3 & Above | 50% |
1 | F | 3 | 3 & Above | 50% |
2 | M | 4 | 4 & Above | 20% |
2 | F | 1 | 1 & Above | 100% |
3 | M | 1 | 1 & Above | 100% |
3 | F | 3 | 3 & Above | 50% |
4 | M | 1 | 1 & Above | 100% |
4 | F | 4 | 4 & Above | 20% |
5 | M | 2 | 2 & Above | 60% |
5 | F | 1 | 1 & Above | 100% |
I have created two calculated columns (Range) & (Percentage) and have then added them to the graph below. Which is exactly how I want my data to be shown. (Percentage of people achieving a value/ above).
However, when I select 'M' or 'F', or Year 1', etc, the data does not change accordingly. How would I go about making the percentages dynamic to the slicer selections?
Solved! Go to Solution.
Hi @devinep5 ,
Please check whether the below screen shot is what you want? If yes, you can follow the below steps to achieve it(You can get my sample pbix file for the full details):
1. Create a dimension table with range column
2. Create a measure as below to get the percentage of per range
Percentage =
VAR _a =
CALCULATE (
COUNT ( 'PT_1819'[Score(1-4)] ),
FILTER (
'PT_1819',
'PT_1819'[Score(1-4)] >= VALUE ( LEFT ( MAX ( 'Range'[Range] ), 1 ) )
)
)
VAR _b =
CALCULATE ( COUNT ( PT_1819[Score(1-4)] ), ALL ( PT_1819 ) )
RETURN
DIVIDE ( _a, _b )
If the above one is not what you want, please provide the calculation logic of calculated column Percentage and make example to explain your expected result when select the different Year and Gender. Thank you.
Best Regards
Rena
Hi @devinep5 ,
Whether both Range and Percentage are calculated column? Could you please share the formulas of these two columns and the calculation logic? Later we may need to create Percentage as measure and update the related formulas in order to make it dynamically based on the selection of slicers. Thank you.
Best Regards
Rena
Hi @devinep5 ,
Please check whether the below screen shot is what you want? If yes, you can follow the below steps to achieve it(You can get my sample pbix file for the full details):
1. Create a dimension table with range column
2. Create a measure as below to get the percentage of per range
Percentage =
VAR _a =
CALCULATE (
COUNT ( 'PT_1819'[Score(1-4)] ),
FILTER (
'PT_1819',
'PT_1819'[Score(1-4)] >= VALUE ( LEFT ( MAX ( 'Range'[Range] ), 1 ) )
)
)
VAR _b =
CALCULATE ( COUNT ( PT_1819[Score(1-4)] ), ALL ( PT_1819 ) )
RETURN
DIVIDE ( _a, _b )
If the above one is not what you want, please provide the calculation logic of calculated column Percentage and make example to explain your expected result when select the different Year and Gender. Thank you.
Best Regards
Rena
Hi,
I can try to help. Share the link from where i can download your PBI file.
@devinep5 , for dynamic segmentation refer
https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization
Hi,
How have you calculated the 2 columns? Share the business logic and also the formulas.
You can keep your range column as is, but make a dynamic measure like this (replace Table with your table name):
Pct At or Above =
VAR __thisscore =
MIN ( Table[Score] )
VAR __total =
CALCULATE ( COUNTROWS ( Table ), ALLSELECTED ( Table ) )
VAR __atorabove =
CALCULATE ( COUNTROWS ( Table ), ALLSELECTED ( Table ), Table[Score] >= __thisscore )
RETURN
DIVIDE ( __atorabove, __total )
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |