possible for a DAX Measure which returns value conditionally based on column value?

[fa82]

Hi Everyone,

I'd really appreciate your help on the following issue

My data has year, quarter, region, revenue, contribution margin:

I'm trying to create a matrix with year+Quarter as columns, and revenue, contribution margin, and contribution margin percentage% in rows. I've created a DAX measure where CM % = contribution margin / Revenue

Currently, the contribution margin% for versusPriorYear (VPY) is not calculated correctly. Is it possible to create a measure which returns a value conditionally. i.e. If the 'year' column value = "2021 versusPriorYear" I'd like this to be calculated:

([2021 contribution margin Sum]/[2021 Revenue Sum]) - ([2020 contribution margin Sum]/[2020 Revenue Sum]) / 

([2020 contribution margin Sum]/[2020 Revenue Sum])

otherwise, the original formula should be used.

I tried to do this with a switch statement, but no value was returned for CM% in versusPriorYear column. If I replace the formula with '0' when the year="2021 versusPriorYear" condition is met, the switch statement works fine and a 0 is in place of the cm%-versusPriorYear cell. This is the switch which is not working:

CM% = 
switch(

SELECTEDVALUE(Financials[Year]),

"2021 VPY", 

DIVIDE(
	DIVIDE(CALCULATE(SUM(Financials[Contribution Margin]), financials[Year]="2021 Actuals"), CALCULATE(SUM(Financials[Revenue]), financials[Year]="2021 Actuals")) -
 
	DIVIDE(CALCULATE(SUM(Financials[Contribution Margin]), financials[Year]="2020 Actuals"), CALCULATE(SUM(Financials[Revenue]), financials[Year]="2020 Actuals")),

DIVIDE(CALCULATE(SUM(Financials[Contribution Margin]), financials[Year]="2020 Actuals"), CALCULATE(SUM(Financials[Revenue]), financials[Year]="2020 Actuals")), "-"
),


DIVIDE(SUM(Financials[Contribution Margin]), SUM(Financials[Revenue]), "-")

)

Is there a different DAX expression I should be using?

I'm open to removing the row data pertaining to versusPriorYear and doing a group calculation in someway using tabular editor if anyone has a simple step-by-step resource. But I'd like to keep the same matrix structure- columns for year/quarter Actuals + column VPY.

Once again, I really appreciate your help

[Anonymous]

Hi @fa82

The switch() formula seems correctly.

The point is whether the below part formula return the correct result.

DIVIDE(
	DIVIDE(CALCULATE(SUM(Financials[Contribution Margin]), financials[Year]="2021 Actuals"), CALCULATE(SUM(Financials[Revenue]), financials[Year]="2021 Actuals")) -
 
	DIVIDE(CALCULATE(SUM(Financials[Contribution Margin]), financials[Year]="2020 Actuals"), CALCULATE(SUM(Financials[Revenue]), financials[Year]="2020 Actuals")),

DIVIDE(CALCULATE(SUM(Financials[Contribution Margin]), financials[Year]="2020 Actuals"), CALCULATE(SUM(Financials[Revenue]), financials[Year]="2020 Actuals")), "-"
),

It's better to split the divide formulas and check if they all return correct value.

Best Regards,

Jay

[Ashish_Mathur]

Hi,

If you can share the first month of every quarter and you also have another column for year, then we can construct a Date column and use the fantastic Date Intelligence functions built into the application.  If that is possible, then share the link from where i can download your PBI file.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with.