Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

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

 

Need Help

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    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. 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous

     

    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