How to add grant total to unstacked bar chart with hierarchy

[Anonymous]

Hi everyone,

I have been trying to add grant totals bar for unstacked (clustered) bar chart that depends on two measures(values), and this is clustered by a hierarchy (Region>Countries>Sites), I could reach the goal for Regions as this solution https://community.powerbi.com/t5/Desktop/Grand-Total-in-Bar-Chart/td-p/612631 , but when I try to mimic it for the following hierarchies it gives me grant totals for every country or site which has zero values. Do you know some trick to achieve this approach?

ExampleFile.pbix

[AllisonKennedy · Accepted solution]

@Anonymous 

Thank you for providing the additional info and sample file. The key was knowing what formula you were using. 

You need to update your measure to: 

Measure = var amount = sum(Amounts[Amount]) return if(SELECTEDVALUE(DimLocations[Unit]) = "Grant Total", CALCULATE(sum(Amounts[Amount]), ALLEXCEPT(Amounts, Amounts[Year])), amount)

You had it as: 

Measure = var amount = sum(Amounts[Amount]) return if(ISBLANK([amount]), CALCULATE(sum(Amounts[Amount]), ALLEXCEPT(Amounts, Amounts[Year])), amount)

But the condition in bold is looking for empty amounts, and as you discovered, this can happen not just in Grand Total row, but where you have no data. So I have updated it to reference Grand Total row by name explicitly.

[Anonymous]

Hi  @Anonymous ,

Your columns [inv] and [olu] have 0 in the data, so the value of 0 will be displayed

Best Regards,

Liu Yang

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

[amitchandak]

@Anonymous , You a create line clustered visual and Line with the marker. And you can have line color same background color.

Refer to this example

https://radacad.com/showing-the-total-value-in-stacked-column-chart-in-power-bi

[AllisonKennedy]

Do you have dimension tables or is it all in one table? Please can you provide sample of your data model with the blank values. You need to find the right modified filter context, probably using ALL() or ALLSELECTED or ALLEXCEPT, etc. Please give as much info as possible about what filters you have on the page too and what to respect and ignore.

[Anonymous]

I attached the sample file