Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all
I created a dax function to calculate the percentage of projects under high risk, medium risk and low risk.
The metric works ok when nothing is selected or I select a country for example, however when I use the slicer and filter by the type of risk (high risk, medium or low), the percentages results in the cards make no sense.
The table used is:
What I would like to have is, if i select high risk in the slicer, then 100% of HR in the card and the rest show 0%, if I select medium risk in the slicer, the card should 100% for medium risk and 0% for the rest, and so on.
Thanks!
Solved! Go to Solution.
Hi @o59393 ,
You can try to use the following measure formulas:
LR =
IF (
"Low Risk" IN VALUES ( 'Table'[Risk] ),
CALCULATE ( COUNT ( 'Table'[Risk] ), 'Table'[Risk] = "Low Risk" )
/ COUNT ( 'Table'[Risk] ),
0
)
MR =
IF (
"Medium Risk" IN VALUES ( 'Table'[Risk] ),
CALCULATE ( COUNT ( 'Table'[Risk] ), 'Table'[Risk] = "Medium Risk" )
/ COUNT ( 'Table'[Risk] ),
0
)
HR =
IF (
"High Risk" IN VALUES ( 'Table'[Risk] ),
CALCULATE ( COUNT ( 'Table'[Risk] ), 'Table'[Risk] = "High Risk" )
/ COUNT ( 'Table'[Risk] ),
0
)
Regards,
Xiaoxin Sheng
HI @o59393 ,
Can you please share some sample data with your measure formula? It is hard to test without any sample data.
How to Get Your Question Answered Quickly
In addition, you can try to use the following measure which will dynamic changes based on your selection:
Measure =
CALCULATE ( COUNT ( Heatmap[Risk] ), VALUES ( Heatmap[Risk] ) )
/ COUNT ( Heatmap[Risk] )
Regards,
Xiaoxin Sheng
hi!
Here you go the sample data
https://www.mediafire.com/file/ipnnoa6ng49gsx6/query_%288%29.xlsx/file
I tried that formula and still game me high %, above 100%
Thanks 🙂
Hi @o59393 ,
You can try to use the following measure formulas:
LR =
IF (
"Low Risk" IN VALUES ( 'Table'[Risk] ),
CALCULATE ( COUNT ( 'Table'[Risk] ), 'Table'[Risk] = "Low Risk" )
/ COUNT ( 'Table'[Risk] ),
0
)
MR =
IF (
"Medium Risk" IN VALUES ( 'Table'[Risk] ),
CALCULATE ( COUNT ( 'Table'[Risk] ), 'Table'[Risk] = "Medium Risk" )
/ COUNT ( 'Table'[Risk] ),
0
)
HR =
IF (
"High Risk" IN VALUES ( 'Table'[Risk] ),
CALCULATE ( COUNT ( 'Table'[Risk] ), 'Table'[Risk] = "High Risk" )
/ COUNT ( 'Table'[Risk] ),
0
)
Regards,
Xiaoxin Sheng
hi!
thanks for the help
I got an error:
The syntax for 'MR' is incorrect. (DAX(IF("Low Risk" IN VALUES(Heatmap[Risk]),CALCULATE(COUNT(Heatmap[Risk]),Heatmap[Risk]="Low Risk")/COUNT(Heatmap[Risk]),0)MR = IF("Medium Risk" IN VALUES(Heatmap[Risk]),CALCULATE(COUNT(Heatmap[Risk]),Heatmap[Risk]="Medium Risk")/COUNT(Heatmap[Risk]),0))).
Thanks for the help!
Hi @o59393 ,
In fact, I attached three measures, you need to create three measures fields to stored these formulas.
Regards,
Xiaoxin Sheng
Hi
Apologies for my ignorance, how do i create the measure fields? Cant see the attachment.
Can you please share how to create them?
Thanks!
@v-shex-msft I created the DAX with the 3 metrics but I still get the same error. Did you do something additional? Can you please shere the pbi.
Thanks!
Hi @o59393 ,
I attached the sample file below, you can try it if it works on your side.
Regards,
Xiaoxin Sheng
User | Count |
---|---|
94 | |
83 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
62 |