Total bar in Hierarchical bar chart

[Chirag4370]

Hello Experts,
I'm stuck on total bars in a hierarchical bar chart. 

Find current & desired output and Data Model from below SnapShots. and used DAX to get Total. 

Thaks in advance.

Values for "Total" derived by DAX:

Val+Total = 
VAR total = CALCULATE(SUM(Data[Val]), ALLSELECTED('Data'))

RETURN
IF(
    SELECTEDVALUE('Date'[DateText]) = "Total" || SELECTEDVALUE(Cat[Cat]) = "Total",
    total,
    SUM(Data[Val])
)

[v-jianpeng-msft · Accepted solution]

Hi, @Chirag4370 

Based on your description, I use the following sample data:

I re-edited the relationship between the tables as shown in the image below:

I use the following DAX expression:

Val+Total =
VAR _data =
    SELECTEDVALUE ( 'Date'[DateText] )
VAR _currentcat =
    MAX ( Data[Cat] )
VAR _total =
    CALCULATE (
        SUM ( Data[Val] ),
        FILTER ( ALLSELECTED ( 'Data' ), 'Data'[Cat] = _currentcat )
    )
RETURN
    IF (
        MAX ( 'Date'[DateText] ) = "Total",
        _total,
        CALCULATE ( SUM ( Data[Val] ), 'Data'[DateText] = _data )
    )

Here are the results:

I've provided the PBIX that I used this time and it would be great if it would help you.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[Chirag4370]

Hello, @v-jianpeng-msft 

Thank You for you guidence.

Actualy I'm looking for GrandTotal also, as I given snapshot tagged with desired output.

is it possible? 

[MFelix · Accepted solution]

HI @Chirag4370 ,

Using  the model you shared you can do the following measure:

Val+Total = 
VAR total = 
  
       CALCULATE(SUM(Data[Val]), ALL(Data))

VAR total_ = 
IF(
    ISFILTERED(Cat[Cat]),
    CALCULATE(SUM(Data[Val]), ALLSELECTED('Date')),
    CALCULATE(SUM(Data[Val]), ALLSELECTED('Cat'))
)

RETURN
SWITCH(TRUE(),
    SELECTEDVALUE('Date'[DateText]) = "Total" && SELECTEDVALUE(Cat[Cat]) = "Total",
    total,
    SELECTEDVALUE('Date'[DateText]) = "Total" || SELECTEDVALUE(Cat[Cat]) = "Total",total_,
    SUM(Data[Val])
)

File attach.

[Chirag4370]

Hello, @MFelix 
Your solution is just amazing. Thank You so much. Truly You are Super User.

[v-jianpeng-msft · Accepted solution]

Hi, @Chirag4370 

Based on your description, I use the following sample data:

I re-edited the relationship between the tables as shown in the image below:

I use the following DAX expression:

Val+Total =
VAR _data =
    SELECTEDVALUE ( 'Date'[DateText] )
VAR _currentcat =
    MAX ( Data[Cat] )
VAR _total =
    CALCULATE (
        SUM ( Data[Val] ),
        FILTER ( ALLSELECTED ( 'Data' ), 'Data'[Cat] = _currentcat )
    )
RETURN
    IF (
        MAX ( 'Date'[DateText] ) = "Total",
        _total,
        CALCULATE ( SUM ( Data[Val] ), 'Data'[DateText] = _data )
    )

Here are the results:

I've provided the PBIX that I used this time and it would be great if it would help you.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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