Latest Enterprise DNA Initiatives


What am I doing wrong with VAR?

trying to calculate Royalties for some specific products and I’m getting the following:

Much appreciated

@SamSPAIN

The issue is that FILTER returns a table and not a scalar value as indicated by the error message.

Thanks @Melissa! any hint on how can I get those VAR working for specific products to apply the royalty %?

The only solution is to use a function that returns a scalar value…

Check https://dax.guide/ because it shows the return value for each DAX function.

Hi @SamSPAIN,

As Melissa said, you can use function to get scalar value instead of table.

Alternatively, If possible, you can share pbix file and I or someone can help you easily.

Cheers!

Are you trying to calculate royalty percentage for individual rows in your table or for the total sales of each product?

Thanks all

So essentially all I’m after is the best code to apply a given royalty % for specific products total sales. I thought I could do this via VAR but this isn’t working as I need a function to return a scalar value rather than table (FILTER issue).

Would you have any hint on how to write this code in a straightforward manner?

Much appreciated

Hi @SamSPAIN,

Since I do not have your model, so I am not sure which columns do you want to use etc. However, you can use below logic.

Lets suppose, you want to apply royalty where productID=1, so I calculated that formula below:

Royality =
VAR salesfilter =
    CALCULATETABLE ( VALUES ( 'Sales'[ProductID] ), Sales[ProductID] = 1 )
RETURN
    IF (
        HASONEVALUE ( 'Sales'[ProductID] ),
        IF (
            VALUES ( 'Sales'[ProductID] ) IN salesfilter,
            ( 1 + 0.2 ) * [Total Sales],
            [Total Sales]
        ),
        BLANK ()
    )

So, you can see that Royality is applied on ProductID=1 in below picture.

image

thanks so much for the code shared @hafizsultan

How about having two conditions? Here I’m struggling a bit on how to combine the IF statement with OR

Royalties =
VAR NewRoyalties =
CALCULATETABLE( VALUES( ‘SPP Hierarchy’[SPP L3 (Text)] ), ‘SPP Hierarchy’[SPP L3 (Text)] = “SmartDate X30” || “SmartDate X40” )
VAR OldRoyalties =
CALCULATETABLE( VALUES( ‘SPP Hierarchy’[SPP L3 (Text)] ), ‘SPP Hierarchy’[SPP L3 (Text)] = “8018i - 8018c” || “SmartDate X60” || “SmartDate X60_128” || “SmartDate X65_128”)
RETURN
IF( OR( (VALUES( ‘SPP Hierarchy’[SPP L3 (Text)] ) IN NewRoyalties, [Sales] * 0.02, [Sales])
VALUES( ‘SPP Hierarchy’[SPP L3 (Text)] ) IN OldRoyalties, [Sales] * 0.04, [Sales] )

)
)

Hi @SamSPAIN,

Please use nested if statements as per below:

image

Since condition is applied on Product1 & Product2, so their results changed.

image

Code:

Royality =
VAR salesfilter1 =
CALCULATETABLE ( VALUES ( ‘Sales’[ProductID] ), Sales[ProductID] = 1 )
VAR salesfilter2 =
CALCULATETABLE ( VALUES ( ‘Sales’[ProductID] ), Sales[ProductID] = 2 )
RETURN
IF (
HASONEVALUE ( ‘Sales’[ProductID] ),
IF (
VALUES ( ‘Sales’[ProductID] ) IN salesfilter1,
( 1 + 0.2 ) * [Total Sales],
IF (
VALUES ( ‘Sales’[ProductID] ) IN salesfilter2,
( 1 + 0.4 ) * [Total Sales],
[Total Sales]
)
),
BLANK ()
)

Hi @hafizsultan, tried your code but couldn’t get the products because True/False condition not met - see below screenshot:

image

Royalties =
VAR NewRoyalties =
CALCULATETABLE( VALUES( Products[SPP L3 (Actual).Level 03] ), Products[SPP L3 (Actual).Level 03] = “SmartDate X30” || “SmartDate X40” )

VAR OldRoyalties =
CALCULATETABLE( VALUES( Products[SPP L3 (Actual).Level 03] ), Products[SPP L3 (Actual).Level 03] = “8018i - 8018c” || “SmartDate X60” || “SmartDate X60_128” || “SmartDate X65_128” )

RETURN

IF( HASONEVALUE( Products[SPP L3 (Actual).Level 03] ),
IF( VALUES( Products[SPP L3 (Actual).Level 03] ) IN NewRoyalties,
[CY Actuals] * 0.02,

IF ( VALUES( Products[SPP L3 (Actual).Level 03] ) IN OldRoyalties,
[CY Actuals] * 0.04,
[CY Actuals]
)
),
BLANK()

)

Any hint here? Thanks a lot

Hi @SamSPAIN,

Can you please try below. I case it does not work, can you please upload sample .pbix file as it will be much easier to dig into the issue.

Royalties =
VAR NewRoyalties =
CALCULATETABLE( VALUES( Products[SPP L3 (Actual).Level 03] ), Products[SPP L3 (Actual).Level 03] = “SmartDate X30” || Products[SPP L3 (Actual).Level 03] = “SmartDate X40” )

VAR OldRoyalties =
CALCULATETABLE( VALUES( Products[SPP L3 (Actual).Level 03] ), Products[SPP L3 (Actual).Level 03] = “8018i - 8018c” || Products[SPP L3 (Actual).Level 03] = “SmartDate X60” || Products[SPP L3 (Actual).Level 03] = “SmartDate X60_128” || Products[SPP L3 (Actual).Level 03] = “SmartDate X65_128” )

RETURN

IF( HASONEVALUE( Products[SPP L3 (Actual).Level 03] ),
IF( VALUES( Products[SPP L3 (Actual).Level 03] ) IN NewRoyalties,
[CY Actuals] * 0.02,

IF ( VALUES( Products[SPP L3 (Actual).Level 03] ) IN OldRoyalties,
[CY Actuals] * 0.04,
[CY Actuals]
)
),
BLANK()

)

Hi @hafizsultan,

This seems to be working! thanks a lot! However, I would like to report 0$ royalties for non-applicable products (those that are out of the VAR… how could I do that?

Royalties =

VAR NewRoyalties =

CALCULATETABLE( VALUES( Products[SPP L3 (Actual).Level 03] ), Products[SPP L3 (Actual).Level 03] = “SmartDate X30” || Products[SPP L3 (Actual).Level 03] = “SmartDate X40” )

VAR OldRoyalties =

CALCULATETABLE( VALUES( Products[SPP L3 (Actual).Level 03] ), Products[SPP L3 (Actual).Level 03] = “8018i - 8018c” || Products[SPP L3 (Actual).Level 03] = “SmartDate X60” || Products[SPP L3 (Actual).Level 03] = “SmartDate X60_128” || Products[SPP L3 (Actual).Level 03] = “SmartDate X65_128” )

RETURN
IF( HASONEVALUE( Products[SPP L3 (Actual).Level 03] ),
IF( VALUES( Products[SPP L3 (Actual).Level 03] ) IN NewRoyalties,
[CY Actuals] * 0.02,
IF ( VALUES( Products[SPP L3 (Actual).Level 03] ) IN OldRoyalties,
[CY Actuals] * 0.04,
[CY Actuals]
)
),
BLANK()
)

Hi @SamSPAIN,

That’s good to know. In this case, just in last if condition replace last [CY Actuals] with 0 as per below. Now, below formula should work fine. Kindly mark it solved if it resolves your query.

Royalties =

VAR NewRoyalties =

CALCULATETABLE( VALUES( Products[SPP L3 (Actual).Level 03] ), Products[SPP L3 (Actual).Level 03] = “SmartDate X30” || Products[SPP L3 (Actual).Level 03] = “SmartDate X40” )

VAR OldRoyalties =

CALCULATETABLE( VALUES( Products[SPP L3 (Actual).Level 03] ), Products[SPP L3 (Actual).Level 03] = “8018i - 8018c” || Products[SPP L3 (Actual).Level 03] = “SmartDate X60” || Products[SPP L3 (Actual).Level 03] = “SmartDate X60_128” || Products[SPP L3 (Actual).Level 03] = “SmartDate X65_128” )

RETURN
IF( HASONEVALUE( Products[SPP L3 (Actual).Level 03] ),
IF( VALUES( Products[SPP L3 (Actual).Level 03] ) IN NewRoyalties,
[CY Actuals] * 0.02,
IF ( VALUES( Products[SPP L3 (Actual).Level 03] ) IN OldRoyalties,
[CY Actuals] * 0.04,
0
)
),
BLANK()
)

Thank you very much @hafizsultan! This worked!

Any hint on how can I show Total Royalties?

image

Hi @SamSPAIN,

Glad that you are making progress. Please use below and it will solve your total issue as well :slight_smile:

Royalties =

VAR NewRoyalties =

CALCULATETABLE( VALUES( Products[SPP L3 (Actual).Level 03] ), Products[SPP L3 (Actual).Level 03] = “SmartDate X30” || Products[SPP L3 (Actual).Level 03] = “SmartDate X40” )

VAR OldRoyalties=

CALCULATETABLE( VALUES( Products[SPP L3 (Actual).Level 03] ), Products[SPP L3 (Actual).Level 03] = “8018i - 8018c” || Products[SPP L3 (Actual).Level 03] = “SmartDate X60” || Products[SPP L3 (Actual).Level 03] = “SmartDate X60_128” || Products[SPP L3 (Actual).Level 03] = “SmartDate X65_128” )

VAR NewRoyalties_Total = 0.2*CALCULATE([CY Actuals]Products[SPP L3 (Actual).Level 03] IN NewRoyalties)


RETURN
IF( HASONEVALUE( Products[SPP L3 (Actual).Level 03] ),
IF( VALUES( Products[SPP L3 (Actual).Level 03] ) IN NewRoyalties,
[CY Actuals] * 0.02,
IF ( VALUES( Products[SPP L3 (Actual).Level 03] ) IN OldRoyalties,
[CY Actuals] * 0.04,
0
)
),
BLANK()
)

Hi @SamSPAIN,

Please ignore above one as I couldn’t type complete formula before hitting enter.

Royalties =

VAR NewRoyalties =

CALCULATETABLE( VALUES( Products[SPP L3 (Actual).Level 03] ), Products[SPP L3 (Actual).Level 03] = “SmartDate X30” || Products[SPP L3 (Actual).Level 03] = “SmartDate X40” )

VAR OldRoyalties=

CALCULATETABLE( VALUES( Products[SPP L3 (Actual).Level 03] ), Products[SPP L3 (Actual).Level 03] = “8018i - 8018c” || Products[SPP L3 (Actual).Level 03] = “SmartDate X60” || Products[SPP L3 (Actual).Level 03] = “SmartDate X60_128” || Products[SPP L3 (Actual).Level 03] = “SmartDate X65_128” )

VAR NewRoyalties_Total = 0.2*CALCULATE([CY Actuals],Products[SPP L3 (Actual).Level 03] IN NewRoyalties)

VAR OldRoyalties_Total = 0.4*CALCULATE([CY Actuals],Products[SPP L3 (Actual).Level 03] IN OldRoyalties)

VAR Total_Royality = NewRoyalties_Total+OldRoyalties_Total

RETURN
IF( HASONEVALUE( Products[SPP L3 (Actual).Level 03] ),
IF( VALUES( Products[SPP L3 (Actual).Level 03] ) IN NewRoyalties,
[CY Actuals] * 0.02,
IF ( VALUES( Products[SPP L3 (Actual).Level 03] ) IN OldRoyalties,
[CY Actuals] * 0.04,
0
)
),
Total_Royality
)

big thanks @hafizsultan! now it’s perfect!

Hi @SamSPAIN,

Glad that it worked for you.

Cheers!