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_tes...
  • 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

     

     

ricardocamargos's avatar
ricardocamargos
Icon for Continued Contributor rankContinued Contributor
8 years ago

IMORTC,

 

Can you provide a sample dataset ?

 

Thanks,

 

Ricardo

IMORTC's avatar
IMORTC
Regular Visitor
8 years ago

here is the pbix file with test data:

 

PBIX file

  • ricardocamargos's avatar
    ricardocamargos
    Icon for Continued Contributor rankContinued Contributor
    8 years ago

    Hi IMORTC,

     

    I couldn't get you PBIX file, it just gave me an error.

     

    Can you just give me a sample dataset ?

     

    Thank u,

     

    Ricardo

  • ricardocamargos's avatar
    ricardocamargos
    Icon for Continued Contributor rankContinued Contributor
    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

     

     

  • IMORTC's avatar
    IMORTC
    Regular Visitor
    8 years ago

    Thx so much! ricardocamargos,

     

    I've been trying to get this to work from so many different angles and formulas, but your solution did the trick!!

     

    Many many thanks for the help :smileyhappy: