Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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, S...
Need Help
Tips and Tricks
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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.

parry2k's avatar
parry2k
Super User
7 years ago

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

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

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      parry2k
      Super User
      7 years ago

      Anonymous can you share your existing measures?

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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)