cancel
Showing results for 
Search instead for 
Did you mean: 
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
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors