Forum Discussion

yukon's avatar
yukon
Icon for Helper I rankHelper I
6 years ago
Solved

How to create a measure with value from another table

Hello Gen,   I'm trying to do simple pivot table with DAX. That's very simple for you all but it's very hard for me to make it. It taking almost 1 week but still can't figure out.  I would like to ...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi yukon ,

     

    We can use the following measures to meet your requirement.

    Measure 1:=SUMX(VALUES(Sell_to_Country),CALCULATE(MAX(Evaluation[PSalesAmt])))

     

     

     

    Measure 2 = IF (
    ISFILTERED ( 'Evaluation'[Sell-to Country Code] )
        || ISFILTERED ( 'Evaluation'[Sell-to Customer Name] ),
    CALCULATE ( AVERAGE ( Evaluation[PSalesAmt] ) ),
    MAXX (
        GROUPBY (
            ALLSELECTED ( 'Calendar' ),
            'Calendar'[Year],
            'Calendar'[Week of Year]
        ),
        CALCULATE ( AVERAGE ( Evaluation[PSalesAvgAmt] ) )
    )
)
     

     

    Then We can use the two measures to replace [PSalesAmt] and [PSalesAvgAmt], the result like this,

     

     

    If you have any other questions, please kindly ask here and we will try to resolve it.

     

    BTW, excel file as attached.


    Best regards,

     

     

v-lid-msft's avatar
v-lid-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi yukon ,

 

We can use the following measures to meet your requirement.

Measure 1:=SUMX(VALUES(Sell_to_Country),CALCULATE(MAX(Evaluation[PSalesAmt])))

 

 

 

Measure 2 = IF (
    ISFILTERED ( 'Evaluation'[Sell-to Country Code] )
        || ISFILTERED ( 'Evaluation'[Sell-to Customer Name] ),
    CALCULATE ( AVERAGE ( Evaluation[PSalesAmt] ) ),
    MAXX (
        GROUPBY (
            ALLSELECTED ( 'Calendar' ),
            'Calendar'[Year],
            'Calendar'[Week of Year]
        ),
        CALCULATE ( AVERAGE ( Evaluation[PSalesAvgAmt] ) )
    )
)
 

 

Then We can use the two measures to replace [PSalesAmt] and [PSalesAvgAmt], the result like this,

 

 

If you have any other questions, please kindly ask here and we will try to resolve it.

 

BTW, excel file as attached.


Best regards,

 

 

  • yukon's avatar
    yukon
    Icon for Helper I rankHelper I
    6 years ago

    Dear v-lid-msft ,

     

    It's awesome and working beautiful. I was spend almost a week to get correct figure but no way. May i ask some question? Is it possible to get same figure without adding Lookup column into "Evaluation" table. It mean we directly take the value from "PlanSales" table and make it measure. I worry about the performance if we add lookup column into "Eveluation". Actually, my table has 1,048,576 rows.

     

    Sorry for my cazy question.

     

    Thank you so much for excel attach.

     

    Regards,