Dynamic Percentage Calculation on Stacked Column Chart based on Mouse hover on the columns data

[SantoshKumar]

Hi Team,

Requirement:

When I hovertip on the chart, the percentage should be calculated as the selected category divided by the total across all categories for that month. So as per below snapshot, when i hover my mouse_pointer on "531", the percentage should be 531/(531+50+50+95)=73%, and secondly if i hover on below "50" value on the same photo should show 50/(531+50+50+95)=7%. I have similar requirement on the same page for few more charts as well.

Note: i was able to create measure for percentage but the problem is that i have to create multiple measures to show percentages for each of them because the values for the chart are different measures/columns from different tables & instead i would like to have a single percentage measure.

Regards

Santosh Kumar P

[v-kelly-msft]

Hi @SantoshKumar ，

You could create a tooltip page and use "multi-row card"visual to realize it:

First create a measure as below:

precision = 
var _total=CALCULATE(SUM('Table'[value]),FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])))
Return
DIVIDE(MAX('Table'[value]),_total)

Finally you would see:

Below is a reference about how to create tooltip page:

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-tooltips

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[SantoshKumar]

Hi @v-kelly-msft ,

My requirement is almost similiar to it. Only change is that instead of One Column , i have more than two as below. I was unable to attach my sample file. could you please let me know what should i need to write in the measure with below mentioned data.

Stacked Chart Visual:

Thanks

Santosh

[v-kelly-msft · Accepted solution]

Hi @SantoshKumar ,

If you,you'd better first unpivot all the category columns;

Then create a measure as below:

precision2 = 
var _total=CALCULATE(SUM('fact table (2)'[Value]),FILTER(ALL('fact table (2)'),'fact table (2)'[Month]=MAX('fact table (2)'[Month])))
Return
DIVIDE(MAX('fact table (2)'[Value]),_total)

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@SantoshKumar , Try a new measure like, assume your current measure is measure 

divide([measure], calculate([measure], allexcept(Table, Table[date])))

[SantoshKumar]

@amitchandak Thanks for your response but its not working.  let me just reiterate my requirement here.

When I hovertip on the chart, the percentage should be calculated as the selected category divided by the total across all categories for that month. So as per below snapshot, when i hover my mouse_pointer on "531", the percentage should be 531/(531+50+50+95)=73%, and secondly if i hover on below "50" value on the same photo should show 50/(531+50+50+95)=7%.

Regards

Santosh 

[amitchandak]

@SantoshKumar , I tried this on my data , seems to be working

Measure 6 = DIVIDE([Sales], CALCULATE([Sales], REMOVEFILTERS('Item'[Category])))