Need Help for showing Weight Completion Percentage

[minhthu1012]

Hi everyone, I have a question related to showing weight completion percentage VS completion percentage in stacked bar chart

Please see this example table below:
- The weight for workstream respectively: PII: 24%, Access: 1%, Migration: 5% and Ingestion: 70%

- 4 unique environment values, in each environment they has 4 unique workstream: PII, Access, Migration, Ingestion

- In each workstream, there are number of schemas

Requirement:
I have 2 slicers: Environment and Workstream. I want to build a stacked bar chart showing all schema in y-axis

In this visual, I want to show: If I select "all" workstream slicer, the value should show Completion Percentage of each schema in every environment 

While If I select only ONE workstream in slicer, the value should show Weight Completion Percentage of each schema in every environment

Expected Result:

when I select "PI" in workstream slicer for "GC" environment, the schema "i_igh" should return 100% (column completion percentage) but if I select all in workstream slicer for "GC" environment, the schema "i_igh" return 24% (column weight completion percentage) because for schema i_igh "PI" done their work 100% but in total "i_igh" actually just finished 24% at all process

I appreciate if someone can show any suggestion on it to resolve!

[v-xuxinyi-msft · Accepted solution]

Hi @minhthu1012

Please try the following steps:

1. Create a calculated table.

Workstream = UNION(VALUES('Table'[Workstream]),ROW("Workstream", "All"))

2. Create two measures as shown below.

Measure = 
IF(
    OR(SELECTEDVALUE(Workstream[Workstream]) = "All", SELECTEDVALUE(Workstream[Workstream]) = BLANK()),
    1,
    IF(
        SELECTEDVALUE(Workstream[Workstream]) = SELECTEDVALUE('Table'[Workstream]), 
        1, 
        0)
)

Measure 2 = 
VAR _N1 = CALCULATE(SUM('Table'[Completion percentage]), FILTER(ALLEXCEPT('Table', 'Table'[Environment], 'Table'[Schema]), [Workstream] = SELECTEDVALUE(Workstream[Workstream])))
VAR _N2 = SUM('Table'[Weight Completion Percentage])
RETURN
IF(OR(SELECTEDVALUE(Workstream[Workstream]) = "All", SELECTEDVALUE(Workstream[Workstream]) = BLANK()), _N2, _N1)

3. Put the measure into the filter so that the visual only shows data where the measure is equal to 1.

Is this the result you expect?

Best Regards,
Community Support Team _Yuliax
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@minhthu1012 , check if these measures can help

Completion % =
AVERAGE('Table'[Completion Percentage])

Weighted Completion % =
SUMX(
'Table',
'Table'[Completion %] *
SWITCH(
TRUE(),
'Table'[Workstream] = "PII", 0.24,
'Table'[Workstream] = "Access", 0.01,
'Table'[Workstream] = "Migration", 0.05,
'Table'[Workstream] = "Ingestion", 0.70,
0
)
)

Dynamic Completion =
IF (
HASONEVALUE('Table'[Workstream]),
[Completion Percentage],
[Weighted Completion Percentage]
)

[minhthu1012]

Hi @amitchandak, Thank you for your help
For this measure, the problem is because when we do stacked bar chart, the y-axis is schema and it returns only unique schema but in real data, the schema name appears in every environment and every workstream. 
So if I select one workstream, your measure is good to show as I expected. But when I select ALL workstream, one schema (in 4 workstreams) only returns percentage of aggregation (Is there any way to show maximum weight percentage is 100% in a bar - for 1 schema and each workstream is different color?)