cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
degnek
Frequent Visitor

Stacked Graph (Not 100% Chart) X-Axis Percentage of column

I am looking for a solution to view percentage of column total (even if it has been filtered) on a stacked bar chart tooltip. I have implemented many different solutions I have come across but to no avail. 

 

Previous solution suggested:

PercentageStories = DIVIDE( SUM('Monthly Totals'[Num Value]),
Calculate(SUM('Monthly Totals'[Num Value]), ALL('Monthly Totals'[Category])),0)


This solution for me is parsing the percentage across the entire chart instead of breaking based on x-axis categories. Is this the intention and/or is it possible to get it to break down by the x-axis values? For instance in my chart I break it down by group, and am needing to look at what percentage each group has for stories that are completed vs not. I would expect my Stories Completed to be showing 90 some percent for each JK and JR Stories Completed relative to their column.

 

Percent Issue.png

 

Example Data.png

7 REPLIES 7
Super User I
Super User I

Re: Stacked Graph (Not 100% Chart) X-Axis Percentage of column

@degnek 

 

Try this..

 

DIVIDE(SUM('Table'[Value]),CALCULATE(SUM('Table'[Value]),ALL('Table'[Category])))
 
If helps, mark  it as a solution
Kudos are nice too.
 
 
degnek
Frequent Visitor

Re: Stacked Graph (Not 100% Chart) X-Axis Percentage of column

@VasTg 

 

That solution then results in each tooltip being %100 no matter which Category you are hovering over. 

 

Try 1 Metric.pngTry 1.png

degnek
Frequent Visitor

Re: Stacked Graph (Not 100% Chart) X-Axis Percentage of column

Correction I was using the DAX of 

Trial% = DIVIDE( SUM('Monthly Totals'[Num Value]),
CALCULATE(SUM('Monthly Totals'[Num Value]),ALLSELECTED('Monthly Totals')))
 
This calculation then splits the percentage over the entire visual, but I need it broken down by column (area)
 
%byGraph.png
 
The PercentageStories that I also tried is the following, which only parses it out based on Category for the visual but it still spans both columns. 
PercentageStories = DIVIDE( SUM('Monthly Totals'[Num Value]),
CALCULATE(SUM('Monthly Totals'[Num Value]),ALL('Monthly Totals'[Area])),0)
 
Community Support
Community Support

Re: Stacked Graph (Not 100% Chart) X-Axis Percentage of column

HI @degnek ,

Maybe you can try to use the below formula, it will calculate the aggregate value of the current category with the total value.

PercentageStories =
DIVIDE (
    CALCULATE (
        SUM ( 'Monthly Totals'[Num Value] ),
        ALLSELECTED ( 'Monthly Totals' ),
        VALUES ( 'Monthly Totals'[Category] )
    ),
    CALCULATE (
        SUM ( 'Monthly Totals'[Num Value] ),
        ALLSELECTED ( 'Monthly Totals' )
    ),
    0
)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
degnek
Frequent Visitor

Re: Stacked Graph (Not 100% Chart) X-Axis Percentage of column

@v-shex-msft ,

Thanks for the suggestion but unfortunately that still provides a split percentage over the whole instead of parsing it out per column. This resulted in respective Stories Completed being the same 55% and 38%, which does not allow for the knowledge that each group got over 90 percent of their stories completed on time. 

Super User I
Super User I

Re: Stacked Graph (Not 100% Chart) X-Axis Percentage of column

@degnek 

 

If my understanding is correct, the DAX I gave you should have worked.

 

Cap11.PNG

 

1. The Measure that shows the % for each column.

2. Expected graph...I hoved over JK and Stories Completed. It show 93.75%. 

3. Same graph in step 2 but displayed as table. Now you see the % in JK and JR add upto 100%.

4. Input data as a single table. 

 

Other than that, I see year 2020 in your graph. Where does it comes from?

 

Are you using the right syntax for DAX? 

 

If this helps, mark it as a solution.

Kudos are nice too.

 

degnek
Frequent Visitor

Re: Stacked Graph (Not 100% Chart) X-Axis Percentage of column

I am not sure where my calculations are going wrong. I filter some additional categories but have tried the measure with nothing filtered and it is still resulting in each section getting 100%. 

 

Measure = DIVIDE(SUM('Monthly Totals'[Num Value]),CALCULATE(SUM('Monthly Totals'[Num Value]),ALL('Monthly Totals'[Category])))
 
Do you see somewhere where my measure is inaccurate? I also took out the year which was being pulled from an additional Date table, with no success. 
 
Thanks
 

2020-01-24 10-53-38_Start.png

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors