Forum Discussion

Dunnes32's avatar
Dunnes32
Frequent Visitor
14 days ago
Solved

% calculations in a matrix

Good afternoon ,

I wonder if someone could help me with a DAX query i am having issues with to show the % of parent at two levels.

I can get the calculations to work individually but when i put them both together (% Combined) the top level % is all showing 100%

 

M_referrals is a count of rows in the table used 

% of parent  group =  var ParentTotal =      CALCULATE(             [M_Referrals],             Allexcept(Referral,Referral[OUTCOME_DURATION_GROUP])     ) return if(     ISINSCOPE(Referral[REFERRAL_SLA_GROUP]),     DIVIDE([M_Referrals],ParentTotal) )
% of parent SLA group =  var ParentTotal =      CALCULATE(             [M_Referrals],             Allexcept(Referral,Referral[REFERRAL_SLA_GROUP])     ) return if(     ISINSCOPE(Referral[OUTCOME_DURATION_GROUP]),     DIVIDE([M_Referrals],ParentTotal) )
% Combined =  var ParentTotal =      CALCULATE(             [M_Referrals],             Allexcept(Referral,Referral[REFERRAL_SLA_GROUP])             ) var SubCat_Total =      CALCULATE(             [M_Referrals],             Allexcept(Referral,Referral[OUTCOME_DURATION_GROUP])     ) var RatioToParent =      if(         ISINSCOPE(Referral[REFERRAL_SLA_GROUP]),         divide([M_Referrals],ParentTotal),         if(             ISINSCOPE(Referral[OUTCOME_DURATION_GROUP]),             divide([M_Referrals],SubCat_Total)         )     ) Return RatioToParent

If anyone could point me in the right direction that would be great.

 

My Next issue will also be to add a Month into the columns, if you could give some advice on this that would be great too thanks

7 Replies

  • v-achippa's avatar
    v-achippa
    Icon for Community Support rankCommunity Support
    10 days ago

    Hi Dunnes32​,

    Thank you for reaching out to Microsoft Fabric Community.

    Thank you sevenhills​, LumericVisuals​ and maruthisp​ for the prompt response.

    As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked?  or let us know if you need any further assistance.

    Thanks and regards,
    Anjan Kumar Chippa

    • Dunnes32's avatar
      Dunnes32
      Frequent Visitor
      10 days ago

      that's fantastic thank you very much for your help.

      It initially didn't work until i created a new report from scratch and all worked fine. I have just noticed that it goes back to 100% if i add a sort order onto one of the columns, do you know what this is and if there is a way to handle it?

      eg sorting the Outcome duration group by another column causes the percentages to be 100%, SLA Group remains correct though

      • Dunnes32's avatar
        Dunnes32
        Frequent Visitor
        10 days ago

        managed to resolve by adding the sort column into the allselected columns.

  • LumericVisuals's avatar
    LumericVisuals
    Icon for Helper II rankHelper II
    13 days ago

    In % of parent group, when you're at the REFERRAL_SLA_GROUP row (the child level), you divide by the OUTCOME_DURATION_GROUP total. In % Combined, that same branch divides by ParentTotal instead — the ALLEXCEPT-on-REFERRAL_SLA_GROUP total, which at that row is identical to the current value. Self-division, hence 100%.

    The fix: swap which variable each branch divides by — reverse of how they're currently paired.

  • Dunnes32's avatar
    Dunnes32
    Frequent Visitor
    13 days ago

    thanks for the reply, unfortunately all the percentages showed as 100% with this measure. I have attached an excel image of what i want the percentages to show. 

    The parent group Referral[REFERRAL_SLA_GROUP] in blue should total 100%, the child category Referral[OUTCOME_DURATION_GROUP] in pink should add up to 100% for each [REFERRAL_SLA_GROUP]. Hope this is possible and makes sense!!

     

  • sevenhills's avatar
    sevenhills
    Icon for Super User rankSuper User
    14 days ago

    I will suggest you to see these articles: 

    https://www.daxpatterns.com/cumulative-total/
    https://www.sqlbi.com/articles/clever-hierarchy-handling-in-dax/ https://www.daxpatterns.com/hierarchies/ 

    See this works and meets your requirement. if not post what is the expected output (like using excel)

    % of Parent Group Dynamic =   SWITCH(       TRUE(),      ISINSCOPE(Referral[OUTCOME_DURATION_GROUP]),          DIVIDE(             [M_Referrals],              CALCULATE([M_Referrals], REMOVEFILTERS(Referral[OUTCOME_DURATION_GROUP]))         ),                   ISINSCOPE(Referral[REFERRAL_SLA_GROUP]),          DIVIDE(             [M_Referrals],              CALCULATE([M_Referrals], REMOVEFILTERS(Referral[REFERRAL_SLA_GROUP]))         ),                -- Default / Grand Total fallback     BLANK() )

    Note: I tried multiple times to post the code properly and it is not coming good format