Forum Discussion

IMORTC's avatar
IMORTC
Regular Visitor
8 years ago
Solved

Fixed value for % ratios

How do I calculate a fixed measure based on a fixed filter like for example below. I need the base turnover calculation to be able to calculate % ratios.

 

Basically what I need is the field "2_test" to be populated with the turnover yet while the slicer filters still applies, in this case Month and Country?

 

 

 The current string I'm using is looking like this:

 

2_test = CALCULATE(SUM('FCC Data'[Actuals - Period]);'FCC Data'[AccountRetail]="Turnover")

  • ricardocamargos's avatar
    ricardocamargos
    8 years ago

    Hi IMORTC.

     

    You can use:

    CALCULATE(SUM(Table1[Actuals - Period]); FILTER(ALL(Table1); Table1[AccountRetail] = "Turnover"))

     

    Be aware you should change this expression if you wanna sum the 2_test based on date/country:

     

    CALCULATE(SUM(Table1[Actuals - Period]); FILTER(ALLEXCEPT(Table1; Table1[Date]; Table1[Country]); Table1[AccountRetail] = "Turnover"))

     

    Ricardo

     

     

  • ricardocamargos's avatar
    ricardocamargos
    8 years ago

    Hi IMORTC,

     

    I could get your file now.

     

    You should use measures instead of calculated columns to achieve this result. Also you just have "Turnover" values for 2 months, do you wanna compare the sum of those 2 months with any filter or just compare the sum of "Turnover" value inside the same month ?

     

    If inside the same month you should have this:

     

    _ACT % = SUM([Actuals - Period]) / CALCULATE(SUM('FCC Data'[Actuals - Period]);FILTER(ALLSELECTED('FCC Data');'FCC Data'[AccountRetail]="Turnover"))

     

    I hope it helps you.

     

    Let me know if u need some help.

     

    Ricardo

     

     

10 Replies

  • ricardocamargos's avatar
    ricardocamargos
    Continued Contributor
    8 years ago

    Hi IMORTC.

     

    You can use:

    CALCULATE(SUM(Table1[Actuals - Period]); FILTER(ALL(Table1); Table1[AccountRetail] = "Turnover"))

     

    Be aware you should change this expression if you wanna sum the 2_test based on date/country:

     

    CALCULATE(SUM(Table1[Actuals - Period]); FILTER(ALLEXCEPT(Table1; Table1[Date]; Table1[Country]); Table1[AccountRetail] = "Turnover"))

     

    Ricardo

     

     

    • IMORTC's avatar
      IMORTC
      Regular Visitor
      8 years ago

      Thank you for prompt help :) That did the trick!

       

       

      • IMORTC's avatar
        IMORTC
        Regular Visitor
        8 years ago

        Another issue has come up... and I am pretty stuck here....

         

        The previous offered solution works like a charm, when you apply the slicers individually. However if I pick two members in the slicer, see below, then it seem to double my ACT figures whereas only calcing the % ratio on one member... pls see below:

         

        Correct view with one member selected in slicer:

         

         

        When picking multiple members in the slicer it shows eg 200% in the % column:

         

         

        My DAX code for the PCT columns look like this:

         

        % = [Actuals - Period]/CALCULATE(SUM('FCC Data'[Actuals - Period]);FILTER(ALLEXCEPT('FCC Data';'FCC Data'[Calendar];'FCC Data'[AM];'FCC Data'[Country]);'FCC Data'[AccountRetail]="Turnover"))