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 everybody,
I am stuck with this problem for a while and I would be really appreciated your help. I have a table like this one below:
Unit | Risk | Control | Assessment |
A | Risk 1 | Control 1.1 | Deficient |
B | Risk 1 | Control 1.2 | Deficient |
B | Risk 1 | Control 1.3 | Deficient |
D | Risk 2 | Control 1.4 | Adequate |
A | Risk 2 | Control 1.5 | Deficient |
C | Risk 3 | Control 1.6 | Adequate |
C | Risk 3 | Control 1.7 | Deficient |
D | Risk 4 | Control 1.8 | Adequate |
A | Risk 4 | Control 1.9 | Adequate |
B | Risk 6 | Control 1.10 | Adequate |
I have risks and controls. Many controls can be mapped to one risk and vice versa. Then I have a pivot table which is filter with Assessment result "Deficient".
Number of controls is 5 but the number of risks is 4. I can get the number of risk by using Subtotal (3; B5:B11). Does anybody know how to get this number in DAX?
Many thanks,
Hi @kimlien3006 ,
You may try this measure.
Total =
VAR TT =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Risk] ),
FILTER ( 'Table', [Unit ] = "A" && [Assessment ] = "Deficient" )
)
+ CALCULATE (
DISTINCTCOUNT ( 'Table'[Risk] ),
FILTER ( 'Table', [Unit ] = "B" && [Assessment ] = "Deficient" )
)
+ CALCULATE (
DISTINCTCOUNT ( 'Table'[Risk] ),
FILTER ( 'Table', [Unit ] = "C" && [Assessment ] = "Deficient" )
)
RETURN
IF (
HASONEVALUE ( 'Table'[Unit ] ),
CALCULATE (
DISTINCTCOUNT ( 'Table'[Risk] ),
FILTER ( 'Table', [Assessment ] = "Deficient" )
),
TT
)
Then you create a matrix as follows.
You can check more details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-stephen-msft and @amitchandak ,
Thanks for your help!.
@v-stephen-msft I tried your way, it actually works but I found another solution which I think it fits better to my case.
So based on the formula @amitchandak gave, I changed it a little bit:
sumx(values(Table[unit]), calculate(distinctcount(Table[risk]), Table [Assessment] = "Deficient"))
With it, it works. But you guys suggest me the idea to come to this solution. Thanks a lot!
Hi @kimlien3006 ,
Very happy to help you.
Please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Stephen Tao
@kimlien3006 , if you want a total as 4
distinctcount(Table[risk])
Total as 5
sumx(values(Table[unit]), calculate(distinctcount(Table[risk])))
Hi @amitchandak I think if I use distinctcount in the table, it will show 5 because I have 5 different risks. Moreover, I only count the defficient assessment
@kimlien3006 , Can you share sample data and sample output in table format?
hi @amitchandak This is the example data. The rule is that many controls can be mapped to one risk. If one control is deficient, then the mapped risk is also difcient.
Unit | Risk | Control | Assessment |
A | Risk 1 | Control 1.1 | Deficient |
B | Risk 1 | Control 1.2 | Deficient |
B | Risk 1 | Control 1.3 | Deficient |
D | Risk 2 | Control 1.4 | Adequate |
A | Risk 2 | Control 1.5 | Deficient |
C | Risk 3 | Control 1.6 | Adequate |
C | Risk 3 | Control 1.7 | Deficient |
D | Risk 4 | Control 1.8 | Adequate |
A | Risk 4 | Control 1.9 | Adequate |
B | Risk 6 | Control 1.10 | Adequate |
then I want to calculate the deficient risks. In Excel, I just simply created the pivot table, filtered the Assessment "Deficient", put the risk in the row and use subtotal (3, B5:B11) to calculate the number of risk which is 4. I hope it is clear enough for you. Please see the excel output file in this link https://drive.google.com/file/d/1XTCujx6HUjB24J84t4os9an2H4Uabole/view?usp=sharing
Thanks,
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 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |