Fixed value for % ratios

[IMORTC]

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 · Accepted solution]

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 · Accepted solution]

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]

Thank you for prompt help 🙂 That did the trick!

[IMORTC]

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"))

[ricardocamargos]

Hi @IMORTC,

How do you relate the members with the data ?

Thanks,

Ricardo

[IMORTC]

Hi Ricardo,

Thx for your help! They are related in the same table... see below:

[ricardocamargos]

@IMORTC,

Can you provide a sample dataset ?

Thanks,

Ricardo

[IMORTC]

here is the pbix file with test data:

PBIX file

[ricardocamargos · Accepted solution]

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]

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 

[ricardocamargos]

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