Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

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.2019_07_30_15_42_19_Window.png 

 

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

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?

1.png

 

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 )

3.png

2.png

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.

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

@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)?2019_08_02_12_34_32_Window.jpg

v-juanli-msft
Community Support
Community Support

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?

1.png

 

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 )

3.png

2.png

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
Not applicable

@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
Super User
Super User

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

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

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@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.CSC.jpg

@Anonymous can you share your existing measures?

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

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

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.