DAX measure for subtotal of group to use in tooltip

[DNKL]

Hi all, 

Power BI newbie here! 🙂 I have already used a lot of the previous posts and videos available here and on Youtube - this is an awesome community! 

I have been struggling with a challenge and have come to the end of my creativity, so I was hoping you might know a solution: 

I am creating a 100% stacked bar chart that contains the values 'Onboarded', 'Access' and 'Analogue' across 6 different modules, one of which is Transfers: 

Since when a customer is onboarded, it means they also have access, I would like to add a measure in the tooltip that shows, for both the 'access' and 'onboarded' sections of the bars, that the 'Total access' = Onboarded+Access, so in the graph above it should be 124. 

I have so far come up with the following DAX measure, that leads to the table on the right: 

Total access =

CALCULATE(

COUNT('Access/Onboarded'[Customer.Customer Code]),

ALL('Access/Onboarded'[Module]),

'Access/Onboarded'[Status (groups)]="Access")

Where the Status (groups) column refers to one where I have grouped Onboarded and Access as 'Access', and Analogue as 'no access'.

However, this clearly does not show the correct number in the graph, nor does it show the correct number in the 'no access' column of the table, since that should be 92.

Do you have any idea how to solve this challenge?

Thank you in advance! 

[v-deddai1-msft]

Hi @DNKL ,

How do you get your  Status (groups) column? I suggest create calculated column by  the following code:

Status (groups) =

IF (

    'Access/Onboarded'[Status] = "Access"

        || 'Access/Onboarded'[Status] = "Onboarded",

    "Access",

    "noAccess"

)

Would you please modify your measure:

Measure =

CALCULATE (

    COUNT ( 'Access/Onboarded'[Customer.Customer Code] );

    FILTER ( 'Access/Onboarded'; 'Access/Onboarded'[Status (groups)] = "Access" )

)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[DNKL]

Hi @v-deddai1-msft ,

Thank you so much for your help! 🙂 I have followed the steps and implemented both the new calculated column and the new DAX formula for the total. This works well for the table on the right of the picture!

However, the measure still does not allow me to show the total of 'Onboarded' and 'Access' (this total access) in the tooltip. 

What I would like to show is this: 

So that in the tooltip for both 'Access' and 'Onboarded' in the graph, the 'Total access' will be equal to 124. 

Do you know if this is possible? 🙂

[v-deddai1-msft · Accepted solution]

Hi @DNKL ,

Would you please modify the Total access to:

Total access  = 

CALCULATE (

    COUNT ( 'Access/Onboarded'[Customer.Customer Code] );

    FILTER ( ALLEXPECT('Access/Onboarded','Access/Onboarded'[Module]), 'Access/Onboarded'[Status (groups)] = "Access" )

)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[DNKL]

Hi @v-deddai1-msft ,

Yes, this works! Thank you very much for your help! 🙂