Forum Discussion

SpiroswayGR's avatar
SpiroswayGR
Icon for Resolver III rankResolver III
5 years ago

Column Total but 100%

Dear community,

 

Do you have any solution how can we show column total but should be on % of 100%.

 

I have this solution : 

 

but i would like to change it like this , so total of subcategory should be 100% (calculation in blue are totally random just to show my expectaion : correct is 16,12% for 2,3% , 4,8% for 0,69% etc)

 

More information : 

  1. Using Matrix
  2. In rows i have 2 categories : 1st general category 2nd : Revenue bands
  3. value is sum of revenue

 

Thank you in advance

Need Help

66 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    SpiroswayGR , With % of sub total and % of total and isinscope you have try measures like

     

     

    ST % = divide([revenue], calculate([revenue], removefilter('Table', 'Table'[Revenue bands])))

     

    % Total = divide([revenue], calculate([revenue], All('Table'))

     

    Final= If(isinscope('Table'[Revenue bands]), [ST % ], [% Total])

     

    https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

     

    Refer for % of total and Sub Total https://youtu.be/6jTildcV2ho

     

    • SpiroswayGR's avatar
      SpiroswayGR
      Icon for Resolver III rankResolver III
      5 years ago

      amitchandak 

      Thank you very much for your time.

      Total revenue is calculated column but i created a simple measure like this

       

      Total_revenue_Net_agg = sum(Reservations_Agreegation[Total_Charge_Net])
      ST % = DIVIDE([Total_revenue_Net_agg],calculate([Total_revenue_Net_agg], REMOVEFILTERS(Reservations_Agreegation, Reservations_Agreegation[Revenue_Band_Net])))

       

      But i have below problem : Multiple table arguments are not allowed in the all / remove filters etc

       

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        5 years ago

        SpiroswayGR , Try like , see if that work

        ST % = DIVIDE([Total_revenue_Net_agg],calculate([Total_revenue_Net_agg], REMOVEFILTERS(Reservations_Agreegation[Revenue_Band_Net])))

         

        or

         

        ST % = DIVIDE([Total_revenue_Net_agg],calculate([Total_revenue_Net_agg], all(Reservations_Agreegation[Revenue_Band_Net])))

         

        or

         

        ST % = DIVIDE([Total_revenue_Net_agg],calculate([Total_revenue_Net_agg], allselected(Reservations_Agreegation[Revenue_Band_Net])))

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi SpiroswayGR ,

     

    Please try this:

    Measure =
VAR _per =
    CALCULATE ( SUM ( 'Table'[Revenue] ), ALLEXCEPT ( 'Table', 'Table'[Category] ) )
RETURN
    IF (
        HASONEVALUE ( 'Table'[Sub] ),
        DIVIDE ( MAX ( 'Table'[Revenue] ), _per ),
        _per
    )

    The final output is shown below:

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • SpiroswayGR's avatar
      SpiroswayGR
      Icon for Resolver III rankResolver III
      5 years ago

      Anonymous thank you very much for your help , but somehow didnt work too.

       

      I have also 3 filters on this specific that i am not sure if that cause the problem.

       

      Just think we have reservations (normal and canceled). In my filters have selection on season (season 2019 , season 2020 ) , hotel name (lets say we have 2 hotels 1 and 2 ) and selection only for normal reservations ( normal = 0 , canceled = 1)

      my results