Forum Discussion
Clustered column chart - unfiltered additional column
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.
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
MaggieCommunity 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.
7 Replies
Anonymous not sure how your existing measures are, add new measure and use ALL
Both % = CALCULATE (<your existing measure>, ALL( YourTable[ServiceLevel] ) )
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.
Anonymous can you share your existing measures?
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
MaggieCommunity 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.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!
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)?
