Forum Discussion
Dynamic Percentage Calculation on Stacked Column Chart based on Mouse hover on the columns data
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
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,
KellyDid I answer your question? Mark my post as a solution!
6 Replies
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,
KellyDid I answer your question? Mark my post as a solution!
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
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,
KellyDid I answer your question? Mark my post as a solution!
SantoshKumar , Try a new measure like, assume your current measure is measure
divide([measure], calculate([measure], allexcept(Table, Table[date])))
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
SantoshKumar , I tried this on my data , seems to be working
Measure 6 = DIVIDE([Sales], CALCULATE([Sales], REMOVEFILTERS('Item'[Category])))
