Recreate this EXCEL LET formula into a Calculated Column

[a68tbird]

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...

[a68tbird]

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!

[a68tbird]

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?

[OliT]

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

[a68tbird]

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. 

[a68tbird · 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)