topic Re: Substract 1 to working measure in DAX Commands and Tips

Substract 1 to working measure

Anonymous — Tue, 25 Oct 2022 15:20:10 GMT

Hi all,

I have a working meaure in power pivot in excel and would now want to substract 1 to the final results but can not find how to do it.

My current measure is:

=Import_Cube[2022_Estimate]/(CALCULATE(SUMX ( Import_Cube, IF ( [Date.Year] ="2021", [Total_CHF], BLANK () ) ),ALL( Import_Cube[Date.Year])))

With 2022_Estimate being a measure as well, Import_Cube a table and the measure is used within a power pivot table.

For example the measure gives me 0.65 and I would want to do 0.65 - 1 to get -0.35.

Thanks in advance.

Best,

Fred

amitchandak — Tue, 25 Oct 2022 16:46:16 GMT

Anonymous , based on what I got.

refer if my blog on the same can help

Anonymous — Wed, 26 Oct 2022 06:42:35 GMT

Thanks for the answer. I however still did not manage to make it work. I am unsure of what part of the other solution is applicable in my case.

Anonymous — Mon, 31 Oct 2022 07:50:38 GMT

I ended doing it with a intermediary step with two measures.

1. Import_Cube[2022_Estimate]/(CALCULATE(SUMX ( Import_Cube, IF ( [Date.Year] ="2021", [Total_CHF], BLANK () ) ),ALL( Import_Cube[Date.Year])))

2. (Import_Cube[21-22 yoy growth helper]-1)*MAX(Import_Cube[2022YTD])