Clustered column chart - unfiltered additional column

[Anonymous]

Hi guys, 

Being a relatively new to Power BI, need a piece of advise. I have this chart that shows Service Level percentage for CS and TS teams, sorted out by customer classification (Priority, Standard and unclassified / unknown customers). However, I would really like to get also unsorted service level into the same chart so it shows the general Service Level without team. As a result I would have a 3rd bar in each section that represent both team service level combined. I would assume I should create a new attribute (some sort of "ALL"), but I am a bit lost on "how". 

Appreciate all userful ideas. 

[Anonymous]

@v-juanli-msft if I may ask additional question. In the source table I also have respective week / year indicator. Can I populate this summary table with that additional measure (so there is also weekly drilldown available)?

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

If you could  create measure as below

count = CALCULATE(COUNT(Sheet1[case]),Sheet1[case] in {"compliant"})

count all = COUNTA(Sheet1[case])

% = [count]/[count all]

count 2 = CALCULATE([count],ALLEXCEPT(Sheet1,Sheet1[cate1]))

count all 2 = CALCULATE([count all],ALLEXCEPT(Sheet1,Sheet1[cate1]))

%2 = [count 2]/[count all 2]

As tested, it is impossible to create a columns chart as you provided with the current data.

could you accept a column and line chart?

Or create a new table,

Table =
VAR new1 =
    SUMMARIZE (
        Sheet1,
        Sheet1[cate1],
        Sheet1[case role],
        "%", CALCULATE ( COUNT ( Sheet1[case] ), Sheet1[case] IN { "compliant" } )
            / COUNTA ( Sheet1[case] )
    )
VAR new2 =
    SUMMARIZE (
        Sheet1,
        Sheet1[cate1],
        "case role", "all",
        "%", CALCULATE (
            COUNT ( Sheet1[case] ),
            FILTER ( ALLEXCEPT ( Sheet1, Sheet1[cate1] ), Sheet1[case] IN { "compliant" } )
        )
            / CALCULATE ( COUNT ( Sheet1[case] ), ALLEXCEPT ( Sheet1, Sheet1[cate1] ) )
    )
RETURN
    UNION ( new1, new2 )

Best Regards
Maggie

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

[Anonymous]

@v-juanli-msft OMG, this works like magic. I was suspecting that additional table would be needed.

Nevetheless, both options work like magic. Thanks so much!

[parry2k]

@Anonymous not sure how your existing measures are, add new measure and use ALL 

Both % = 
CALCULATE (<your existing measure>, ALL( YourTable[ServiceLevel] ) )

[Anonymous]

@parry2k thanks for coming back this quick.

The initial table looks like this. To calculate service level I just sum up compliant milestones and then divide them by total count of IDs (total number of incoming cases, that is). Additionally before chart design I sum up CSC roles to CS and TS by grouping bins.

[parry2k]

@Anonymous can you share your existing measures?

[Anonymous]

@parry2k First of all I do calculation on how many cases were closed according the milestone (have "Compliant" in the status).

Closed OK =

CALCULATE(

    COUNTA('Cases closed'[Case Milestone]);

    'Cases closed'[Case Milestone] IN { "Compliant" }

)

Then I divide that one with total count

CSC SLA % = DIVIDE([Closed OK];'Cases closed'[Case ID Count];0)