Forum Discussion

RZAU's avatar
RZAU
Frequent Visitor
3 years ago
Solved

Nested Iterators

Hello there

 

I am very new to DAX. I just couldn't figure our the difference between below two codes. Why they produce different results?

 

Code 1:

Sales Amount= SUMX(Sales, Sales[Quantity]*Sales[Net Price]) // Calculate sales amount first
Avg Sales by Customer = AVERAGEX(Customer, [Sales Amount]) // then calculate average

 

Code 2: Combine them together

Avg Sales by Customer=
AVERAGEX(
          Customer,
          SUMX(

                     Sales,
                      Sales[Quantity]*Sales[Net Price]

)

)

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    I think, if you write the second one like below,

     

     

     

     

    Avg Sales by Customer =
AVERAGEX (
    Customer,
    CALCULATE ( SUMX ( Sales, Sales[Quantity] * Sales[Net Price] ) )
)

     

     

     

     

     it will show the same result as the first one.

     

    If the measure itself is referenced by another measures (like the first one), CALCULATE is auto-created in order to execute context transition.

    Instead of referencing the measure itself, if you write the whole formula, CALCULATE has to be included in order to execute context transition.

     

    I hope it helps.

     

     

1 Reply

Replies have been turned off for this discussion
  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi,

    I think, if you write the second one like below,

     

     

     

     

    Avg Sales by Customer =
AVERAGEX (
    Customer,
    CALCULATE ( SUMX ( Sales, Sales[Quantity] * Sales[Net Price] ) )
)

     

     

     

     

     it will show the same result as the first one.

     

    If the measure itself is referenced by another measures (like the first one), CALCULATE is auto-created in order to execute context transition.

    Instead of referencing the measure itself, if you write the whole formula, CALCULATE has to be included in order to execute context transition.

     

    I hope it helps.