cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
a68tbird
Resolver I
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

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)

 

 

 

View solution in original post

5 REPLIES 5
a68tbird
Resolver I
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!

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?

 

Hi @a68tbird 

Did you define __EIGHTYFIVE &  __CREDITCARDFEES in this measure?

You can try measure like

measure=

var __EIGHTYFIVE=<your expression>

var __CREDITCARDFEES=<your expression>

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

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. 

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)

 

 

 

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

BizApps LATAM 2023

Business Application LATAM Summit 2023

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

Power Platform Bootcamp

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.