cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
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 RiskControlAssessment 
ARisk 1Control 1.1Deficient 
BRisk 1Control 1.2Deficient 
BRisk 1Control 1.3Deficient 
DRisk 2Control 1.4Adequate
ARisk 2Control 1.5Deficient 
CRisk 3Control 1.6Adequate
CRisk 3Control 1.7Deficient 
DRisk 4Control 1.8Adequate
ARisk 4Control 1.9Adequate
BRisk 6Control 1.10Adequate


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". 

 

Annotation 2020-10-13 164238.jpg

 

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
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.

8.png

 

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

Super User IV
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])))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

 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?



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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 RiskControlAssessment 
ARisk 1Control 1.1Deficient 
BRisk 1Control 1.2Deficient 
BRisk 1Control 1.3Deficient 
DRisk 2Control 1.4Adequate
ARisk 2Control 1.5Deficient 
CRisk 3Control 1.6Adequate
CRisk 3Control 1.7Deficient 
DRisk 4Control 1.8Adequate
ARisk 4Control 1.9Adequate
BRisk 6Control 1.10Adequate

 

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,

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

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

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors