cancel
Showing results for
Did you mean:
Resolver I

## Recreate this EXCEL LET formula into a Calculated Column

Hello all,

I have a formula in an Excel document that I need to translate for use in a calculated column for a PowerBI report. I'm defining variables using the LET function, so am not entirely sure how I would write the M or DAX for this.

``````=LET(
Eighty,
[@[Gross Profit]] * 80%,
TwentyFive,
[@[Gross Profit]] - ( [@Price] * 25% ),
Twenty,
[@[Gross Profit]] - ( @[Price] * 20% ),
Fifteen,
[@[Gross Profit]] - ( [@Price] * 15% ),
Standard,
IF(
[@Price] * 15% > [@[Gross Profit]],
0,
( [@[Gross Profit]] - [@Price] * 15% ) * 70%
),
IFS(
OR(
[@Name] = "Property1",
[@Name] = "Property2",
[@Name] = "Property3",

),
Eighty,
OR(
[@Name] = "Property4",
[@Name] = "Property5",

),
TwentyFive,
[@Name] = "Property6",
Twenty,
[@Name] = "Property7",
Fifteen,
TRUE,
Standard
)
)``````

Any suggestions on how I could recreate this? I'm assuming it has to be a calculated column, not a measure, due to the row context required. But maybe I'm wrong...

1 ACCEPTED SOLUTION
Resolver I

SOLVED!

I don't know why when I tried earlier to create variables in a calculated column it didn't work, but I tried again, and this time it worked, and I was able to include whatever conditions I needed. Here's the final DAX for the calculated column, for anyone interested:

``````Artist Net =
VAR __GROSSPROFIT = (StoreSales[StoreTotal] - StoreSales[Product Cost])
VAR __EIGHTY = __GROSSPROFIT*.8
VAR __TWENTYFIVE = __GROSSPROFIT - (StoreSales[StoreTotal]*.25)
VAR __TWENTY = __GROSSPROFIT - (StoreSales[StoreTotal]*.2)
VAR __EIGHTYFIVE = __GROSSPROFIT*.85
VAR __STANDARD = IF(StoreSales[StoreTotal]*.15>__GROSSPROFIT,0,__GROSSPROFIT-StoreSales[StoreTotal]*.15)*.7
VAR __CREDITCARDFEES = StoreSales[StoreTotal]*.0297
RETURN
SWITCH(
TRUE(),
StoreSales[ProductID] = "11-4239",StoreSales[StoreTotal]-__CREDITCARDFEES, //DONATIONS
StoreSales[PropertyName] = "Property1" ||
StoreSales[PropertyName] = "Property2" ||
StoreSales[PropertyName] = "Property3",0.00,
StoreSales[PropertyName] = "Property4" ||
StoreSales[PropertyName] = "Property5", __EIGHTY - __CREDITCARDFEES,
StoreSales[PropertyName] = "Property6" ||
StoreSales[PropertyName] = "Property7", __TWENTYFIVE - __CREDITCARDFEES,
StoreSales[PropertyName] = "Property8", __TWENTY - __CREDITCARDFEES,
StoreSales[PropertyName] = "Property9" && StoreSales[RevenueStream] <>"Donation" , __EIGHTYFIVE - __CREDITCARDFEES,
__STANDARD)``````

5 REPLIES 5
Resolver I

I think I got closer to a solution.  Instead of a Calculated Column from within the PowerQuery editor (where I couldn't figure out how to use variables at all), I instead added a column from the Data view.  So I can assign the variables, but I'm having trouble figuring out how to establish row context.

My DAX code (simplified for testing) is:

``````Net =
VAR __GrossProfit = SUMX(StoreSales,StoreSales[StoreTotal])-SUMX(StoreSales,StoreSales[Product Cost])
VAR __Eighty = __GrossProfit*.8

RETURN
IF(StoreSales[PropertyName] = "PropertyName1",__Eighty,0)``````

This returns the same value for each row - no row context.

Thanks for any help!

Resolver I

I managed to get a working solution, but hoping someone might help with one final tweak I need.  Here is my DAX measure:

``````ArtistNet =
VAR __EIGHTY = [GrossProfit] * .8
VAR __TWENTYFIVE = [GrossProfit] - ( StoreSales[Total Store Sales] * .25 )
VAR __TWENTY = [GrossProfit] - ( StoreSales[Total Store Sales] * .2 )
VAR __EIGHTYFIVE = [GrossProfit] * .85
VAR __STANDARD =
IF (
StoreSales[Total Store Sales] * .15 > [GrossProfit],
0,
[GrossProfit] - ( StoreSales[Total Store Sales] * .15 )
) * .7
VAR __CREDITCARDFEES = StoreSales[Total Store Sales] * .0297
RETURN
IF (
MAX ( StoreSales[PropertyName] ) = "Property1"
|| MAX ( StoreSales[PropertyName] ) = "Property2"
|| MAX ( StoreSales[PropertyName] ) = "Property3",
__EIGHTY - __CREDITCARDFEES,
IF (
MAX ( StoreSales[PropertyName] ) = "Property4"
|| MAX ( StoreSales[PropertyName] ) = "Property5",
__TWENTYFIVE - __CREDITCARDFEES,
IF (
MAX ( StoreSales[PropertyName] ) = "Property6",
__TWENTY - __CREDITCARDFEES,
IF (
MAX ( StoreSales[PropertyName] ) = "Property7",
__EIGHTYFIVE - __CREDITCARDFEES,
__STANDARD - __CREDITCARDFEES
)
)
)
)``````

So I need to include a further condition.  Essentially, that last IF statement needs to be:

IF(MAX(StoreSales[PropertyName]) = "Property7" && StoreSales[RevenueStream] <>"Donation", __EIGHTYFIVE - __CREDITCARDFEES

I can't seem to find the right syntax to include this additional condition.  Any suggestions?

Resolver I

Did you define __EIGHTYFIVE &  __CREDITCARDFEES in this measure?

You can try measure like

measure=

return

IF(MAX(StoreSales[PropertyName]) = "Property7" && min( StoreSales[RevenueStream] )<>"Donation", __EIGHTYFIVE - __CREDITCARDFEES)

usually, we use aggregate functions such as max(), min() or selectedvalue() to get the value of the current row.

Regards,

OliT

Resolver I

Thanks @OliT, however that still doesn't work.  It seems that using MAX() or MIN() doesn't segregate thoses rows where RevenueStream <> "Donation".  I tried simplifying everything, to try and just count the rows that contain "Donation", (using TRIM and other text formatting functions to eliminate any possibility of mismatches) and COUNTROWS. I even tried the actual product ID:

``````Test =
IF(Min(StoreSales[ProductID])="11-4239",COUNTROWS(StoreSales),0)``````

but this didn't work either - the result was zero.

So I tried:

``````Test =
IF(CONTAINS(StoreSales,StoreSales[ProductID],"11-4239"),COUNTROWS(StoreSales),0)``````

This at least returned a number, but it's the total number of rows of the property that has that product ID.  (eg. if Property7 has 5 rows of sales, and two of them are of product ID 11-4239, it still returns 5).

I'm really stumped on how to write this.

Resolver I

SOLVED!

I don't know why when I tried earlier to create variables in a calculated column it didn't work, but I tried again, and this time it worked, and I was able to include whatever conditions I needed. Here's the final DAX for the calculated column, for anyone interested:

``````Artist Net =
VAR __GROSSPROFIT = (StoreSales[StoreTotal] - StoreSales[Product Cost])
VAR __EIGHTY = __GROSSPROFIT*.8
VAR __TWENTYFIVE = __GROSSPROFIT - (StoreSales[StoreTotal]*.25)
VAR __TWENTY = __GROSSPROFIT - (StoreSales[StoreTotal]*.2)
VAR __EIGHTYFIVE = __GROSSPROFIT*.85
VAR __STANDARD = IF(StoreSales[StoreTotal]*.15>__GROSSPROFIT,0,__GROSSPROFIT-StoreSales[StoreTotal]*.15)*.7
VAR __CREDITCARDFEES = StoreSales[StoreTotal]*.0297
RETURN
SWITCH(
TRUE(),
StoreSales[ProductID] = "11-4239",StoreSales[StoreTotal]-__CREDITCARDFEES, //DONATIONS
StoreSales[PropertyName] = "Property1" ||
StoreSales[PropertyName] = "Property2" ||
StoreSales[PropertyName] = "Property3",0.00,
StoreSales[PropertyName] = "Property4" ||
StoreSales[PropertyName] = "Property5", __EIGHTY - __CREDITCARDFEES,
StoreSales[PropertyName] = "Property6" ||
StoreSales[PropertyName] = "Property7", __TWENTYFIVE - __CREDITCARDFEES,
StoreSales[PropertyName] = "Property8", __TWENTY - __CREDITCARDFEES,
StoreSales[PropertyName] = "Property9" && StoreSales[RevenueStream] <>"Donation" , __EIGHTYFIVE - __CREDITCARDFEES,
__STANDARD)``````

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors