Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kimlien3006
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
v-stephen-msft
Community Support
Community Support

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

amitchandak
Super User
Super User

@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 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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.