cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Stacked bar chart - % of total

This might be a simple fix, im not sure. 

 

I have a simple table (shown below). Essentially what i want is a stacked bar chart show progress to the total.

 

Label                      Tasks Needed                     Tasks Completed

Name                     10                                       5

 

 

When i create the bar chart, the %'s are using a total of 15 instead of 10. So it would show 30% instead of 50%, when comparing completed tasks to total. 

 

Any advice? (P.S. I know this can be achieved in a bullet chart but IMO this is more visually appealing for me audience.)

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Stacked bar chart - % of total

Hi @thampton

Workaround:

I make a copy of "Sheet6" as "Sheet7", in "Sheet7", select "Tasks Needed" and "Tasks Completed" columns, then "Unpivot columns",

6.png7.png

Close &&Apply, create a measure in "Sheet7",

Measure =
VAR percentage =
    CALCULATE (
        SUM ( Sheet7[Value] ),
        FILTER (
            ALL ( Sheet7 ),
            Sheet7[Label ] = MAX ( Sheet7[Label ] )
                && Sheet7[Attribute] = "Tasks Completed"
        )
    )
        / CALCULATE (
            SUM ( Sheet7[Value] ),
            FILTER (
                ALL ( Sheet7 ),
                Sheet7[Label ] = MAX ( Sheet7[Label ] )
                    && Sheet7[Attribute] = "Tasks Needed"
            )
        )
RETURN
    IF ( MAX ( Sheet7[Attribute] ) = "Tasks Completed", percentage, 1 - percentage )

Select data format for this measure as "percentage",

Add columns and measures from Sheet7 in the "Stacked bar chart" as below

 

8.png

 

Best Regards

Maggie

View solution in original post

5 REPLIES 5
Highlighted
Super User IX
Super User IX

Re: Stacked bar chart - % of total

Given the information provided, I'm not following why you are getting a 15 instead of 10. You can display a % of total by clicking the little drop down next to the column or measure in the fields area under VISUALIZATIONS and choose Show Value As | Percentage of Grand Total


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Highlighted
Helper III
Helper III

Re: Stacked bar chart - % of total

Here is a better example. I am using the 100% stacked column chart (sorry i misspoke in title). 

 

 

I have two values: one value is the "needed" at 784, one value is the "completed" at 64.

 

The data label inside the "completed" is 7.55% which is wrong. It is using (784+64) as the total instead of using 64/784 = 8.2%.

Highlighted
Super User V
Super User V

Re: Stacked bar chart - % of total

Hi,

 

Try this measure

 

Progress (%) =SUM(Data[Tasks Completed])/SUM(Data[Tasks Needed])

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Community Support
Community Support

Re: Stacked bar chart - % of total

Hi @thampton

Workaround:

I make a copy of "Sheet6" as "Sheet7", in "Sheet7", select "Tasks Needed" and "Tasks Completed" columns, then "Unpivot columns",

6.png7.png

Close &&Apply, create a measure in "Sheet7",

Measure =
VAR percentage =
    CALCULATE (
        SUM ( Sheet7[Value] ),
        FILTER (
            ALL ( Sheet7 ),
            Sheet7[Label ] = MAX ( Sheet7[Label ] )
                && Sheet7[Attribute] = "Tasks Completed"
        )
    )
        / CALCULATE (
            SUM ( Sheet7[Value] ),
            FILTER (
                ALL ( Sheet7 ),
                Sheet7[Label ] = MAX ( Sheet7[Label ] )
                    && Sheet7[Attribute] = "Tasks Needed"
            )
        )
RETURN
    IF ( MAX ( Sheet7[Attribute] ) = "Tasks Completed", percentage, 1 - percentage )

Select data format for this measure as "percentage",

Add columns and measures from Sheet7 in the "Stacked bar chart" as below

 

8.png

 

Best Regards

Maggie

View solution in original post

Highlighted
Helper III
Helper III

Re: Stacked bar chart - % of total

This is awesome! Thanks

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors