Stacked bar chart - % of total

[thampton]

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.)

[v-juanli-msft · Accepted solution]

Hi @thampton

Workaround:

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

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

Best Regards

Maggie

[thampton]

This is awesome! Thanks

[Ashish_Mathur]

Hi,

Try this measure

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

Hope this helps.

[Greg_Deckler]

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

[thampton]

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%.