Calculate % difference between one value and a number of others

[villa1980]

Hi all,

 I have the below table created using parameters and what I want to do is show the % increase or decrease of November to February against October.
 I have a feeling i may have to create variables which I have done for Oct and the other months ( Oct has a P as a filter and brings back Document count, other months have a Y as a filter bringing back the document count).

When I create the calculation using the Variables I get an inifintiy return or the same values.
Hope this makes sense what I want, difficult trying to search it in Google 🙂

Month     Inv     

Oct (P)     200
Nov(Y)     150

Dec(Y)      175

Jan(Y)       250

Feb(Y)       57

These are the variables created

VAR Pre = CALCULATE([2 or 3 Tyres],'PBI VW_DUMMY'[promo] = "P")

VAR Promo = CALCULATE([2 or 3 Tyres],'PBI VW_DUMMY'[promo] = "Y")
RETURN
(Pre - Promo ) / Pre

Thanks

Alex

[v-ssriganesh · Accepted solution]

Hi @villa1980,
Thank you for reaching out to the Microsoft Fabric Community Forum.

I’ve reproduced your scenario using sample data and successfully achieved the expected output based on your requirements to calculate the % difference between October (with promo = "P") and other months (with promo = "Y") against October’s value.

I used the following DAX measures:

% Difference vs October =

VAR Pre = CALCULATE(

    [2 or 3 Tyres],

    'PBI VW_DUMMY'[promo] = "P",

    'PBI VW_DUMMY'[Month] = "Oct",

    ALL('PBI VW_DUMMY') -- Ensures Pre is calculated independently

)

VAR Promo = CALCULATE(

    [2 or 3 Tyres],

    'PBI VW_DUMMY'[promo] = "Y",

    ALLEXCEPT('PBI VW_DUMMY', 'PBI VW_DUMMY'[Month]) -- Keeps Month context

)

RETURN

IF(

    SELECTEDVALUE('PBI VW_DUMMY'[promo]) = "Y", -- Safely checks promo value in current context

    DIVIDE(Promo - Pre, Pre, 0) * 100,

    BLANK()

)

For your reference, I’ve attached a sample .pbix file.

If you find this information useful, please “Accept as solution” and give it a "kudos" to assist others in locating it easily.
Thank you.

[villa1980]

That is ok about the sad face, I had that when trying to work this out 🙂

Thank you for the reply, unfortunately this returns a 1 for October. Also I cannot use Sum because the [2 or 3 tyres] is a measure from a metrics table I have created

[bhanu_gautam]

@villa1980 , Try using

DAX
VAR OctCount = CALCULATE([DocumentCount], 'PBI VW_DUMMY'[promo] = "P")
VAR NovCount = CALCULATE([DocumentCount], 'PBI VW_DUMMY'[promo] = "Y" && 'PBI VW_DUMMY'[Month] = "Nov")
VAR DecCount = CALCULATE([DocumentCount], 'PBI VW_DUMMY'[promo] = "Y" && 'PBI VW_DUMMY'[Month] = "Dec")
VAR JanCount = CALCULATE([DocumentCount], 'PBI VW_DUMMY'[promo] = "Y" && 'PBI VW_DUMMY'[Month] = "Jan")
VAR FebCount = CALCULATE([DocumentCount], 'PBI VW_DUMMY'[promo] = "Y" && 'PBI VW_DUMMY'[Month] = "Feb")

VAR NovDiff = (NovCount - OctCount) / OctCount
VAR DecDiff = (DecCount - OctCount) / OctCount
VAR JanDiff = (JanCount - OctCount) / OctCount
VAR FebDiff = (FebCount - OctCount) / OctCount

RETURN
SWITCH(
TRUE(),
'PBI VW_DUMMY'[Month] = "Nov", NovDiff,
'PBI VW_DUMMY'[Month] = "Dec", DecDiff,
'PBI VW_DUMMY'[Month] = "Jan", JanDiff,
'PBI VW_DUMMY'[Month] = "Feb", FebDiff,
BLANK()
)

[villa1980]

Thank-you for the reply, problem is as this is using a parameter then these months will change for other choices within the parameter

[freginier]

Hey Alex!

Try this formula: 

% Change from October =
VAR Pre =
CALCULATE( SUM( 'PBI VW_DUMMY'[Inv] ), 'PBI VW_DUMMY'[promo] = "P" )

VAR Promo =
CALCULATE( SUM( 'PBI VW_DUMMY'[Inv] ), 'PBI VW_DUMMY'[promo] = "Y" )

RETURN
IF( NOT ISBLANK(Pre), (Promo - Pre) / Pre, BLANK() )

SUM() Function:Ensures numerical aggregation instead of possibly returning a table.

 IF( NOT ISBLANK(Pre), ... 😞 Prevents division errors when Pre (October) is blank.

Uses SUM to ensure correct calculations:CALCULATE(SUM(....)) ensures correct context filtering instead of returning unexpected results

Hope this helps!

😁😁

[freginier]

Sorry, did not mean to put that random crying emoji in teh middle haha