What am I doing wrong with VAR?

SamSPAIN · February 17, 2020, 3:15pm

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

Much appreciated

Melissa · February 17, 2020, 3:26pm

@SamSPAIN

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

SamSPAIN · February 17, 2020, 3:27pm

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

Melissa · February 17, 2020, 3:33pm

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.

hafizsultan · February 17, 2020, 11:25pm

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!

DaveC · February 18, 2020, 12:12am

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

SamSPAIN · February 18, 2020, 9:19am

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

hafizsultan · February 18, 2020, 9:56am

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.

SamSPAIN · February 18, 2020, 12:30pm

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] )

)
)

hafizsultan · February 18, 2020, 8:25pm

Hi @SamSPAIN,

Please use nested if statements as per below:

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

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 ()
)

SamSPAIN · February 19, 2020, 4:53pm

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

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

hafizsultan · February 19, 2020, 9:03pm

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()

)

SamSPAIN · February 20, 2020, 5:37am

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()
)

hafizsultan · February 20, 2020, 6:43am

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()
)

SamSPAIN · February 20, 2020, 11:24am

Thank you very much @hafizsultan! This worked!

Any hint on how can I show Total Royalties?

hafizsultan · February 20, 2020, 9:01pm

Hi @SamSPAIN,

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

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()
)

hafizsultan · February 20, 2020, 9:06pm

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
)

SamSPAIN · February 21, 2020, 8:31am

big thanks @hafizsultan! now it’s perfect!

hafizsultan · February 21, 2020, 8:40am

Hi @SamSPAIN,

Glad that it worked for you.

Cheers!