Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Credo
Helper I
Helper I

VAR different result than without it

I've been slowly learning to use variables however I've run into a result that I dont understand 

 

I have a table with Sales by Manager that is filtered by TopN 10 Managers based on sales. 

 

I have the following measures, one just a sales total, another is two display a total that accounts for all sales not just top 10 managers. 

 

 

Sum of sales =
SUM ( 'FactSales'[Sales] )

Sales Top 10 =
IF (
    HASONEVALUE ( 'FactSales'[Manager] );
    'FactSales'[Sum of sales];
    CALCULATE ( 'FactSales'[Sum of sales]; ALL ( 'FactSales'[Manager] ) )
)

 

This measure displays the correct total for all sales

 

I tried writting the same measure with variables

 

 

 

 

Sales Top 10 =
VAR Sales =
    SUM ( 'FactSales'[Sales] )
RETURN
    IF (
        HASONEVALUE ( 'FactSales'[Manager] );
        Sales;
        CALCULATE ( Sales; ALL ( 'FactSales'[Manager] ) )
    )

 

However it only shows the total for top 10. 

 

Whats causing this behaviour? 

 

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

Hi @Credo

 

When a variable is defined with VAR, it is evaluated in the context where it is defined, then it is effectively a constant when used in any subsequent expressions. Currently there is no way to define a measure locally within another measure, so I would recommend sticking with Sum of Sales as a separate measure and referencing that within Sales Top 10.

 

To explain further, in your second example, after the variable Sales is defined as SUM ( 'FactSales'[Sales] ), the expression CALCULATE ( Sales; ALL ( 'FactSales'[Manager] ) ) gives exactly the same result as Sales, since Sales is effectively a constant, and modifying filters makes no difference to a constant.

 

Regards,

Owen

 

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

1 REPLY 1
OwenAuger
Super User
Super User

Hi @Credo

 

When a variable is defined with VAR, it is evaluated in the context where it is defined, then it is effectively a constant when used in any subsequent expressions. Currently there is no way to define a measure locally within another measure, so I would recommend sticking with Sum of Sales as a separate measure and referencing that within Sales Top 10.

 

To explain further, in your second example, after the variable Sales is defined as SUM ( 'FactSales'[Sales] ), the expression CALCULATE ( Sales; ALL ( 'FactSales'[Manager] ) ) gives exactly the same result as Sales, since Sales is effectively a constant, and modifying filters makes no difference to a constant.

 

Regards,

Owen

 

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.