cancel
Showing results for
Did you mean:
Helper II

## [HELP] Which formula is similar to SUBTOTAL(3; ...)

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,

7 REPLIES 7
Microsoft

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.

Helper II

@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!

Microsoft

Hi @kimlien3006 ,

Please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Stephen Tao

Super User IV

@kimlien3006 , if you want a total as 4

distinctcount(Table[risk])

Total as 5

sumx(values(Table[unit]), calculate(distinctcount(Table[risk])))

Proud to be a Super User!

Helper II

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

Super User IV

@kimlien3006 , Can you share sample data and sample output in table format?

Proud to be a Super User!

Helper II

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,

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!