Forum Discussion

Dunnes32's avatar
Dunnes32
Frequent Visitor
2 days ago

% 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

3 Replies

  • sevenhills's avatar
    sevenhills
    Super User
    2 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

  • Dunnes32's avatar
    Dunnes32
    Frequent Visitor
    2 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!!

     

  • LumericVisuals's avatar
    LumericVisuals
    New Member
    1 day 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.