Forum Discussion

Mohsin2022's avatar
Mohsin2022
Icon for Helper I rankHelper I
4 years ago
Solved

SUM RESULT SHOULD BE SHOWN AGAINST ONE ROW

  I WANT TO SUM DATA BASED ON PROD ID BUT SUM VALUE SHOULD BE SHOWN ON SINGLE ROW    NOW ALL THE LINE HAVE SAME VALUE 
Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    Mohsin2022 
    You need first to add an index column using power query.

    Then creat a new calculated column using dax

    Result = 
VAR CurrentIDTable = CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[ProdID] ) )
VAR CurrentIndex = Data[Index]
VAR FirstIndex = MINX ( CurrentIDTable, Data[Index] )
RETURN
    IF ( CurrentIndex = FirstIndex, Data[Current] )
  • tamerj1's avatar
    tamerj1
    4 years ago

    Mohsin2022 
    Please use

    BOMID New =
CALCULATE ( MAX ( Data[BOMID] ), ALLEXCEPT ( Data, Data[ProdID] ) )
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Hi Mohsin2022 
Please connect both tables via ItemID (It would be many to many 2way). Then create the column in Table1

 

Last Price Look up =
MAXX (
    FILTER ( RELATEDTABLE ( Table2 ), Table2[Last Price] <> BLANK () ),
    Table2[Last Price]
)

Also this one should simply work

Last Price Look up =
MAXX (
    RELATEDTABLE ( Table2 ),
    Table2[Last Price]
)

 

Mohsin2022's avatar
Mohsin2022
Icon for Helper I rankHelper I
4 years ago

Thanks dear its working

Now if i want to add average price column in table 2 like i did for last price

Then i will look up average price as well 

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Yes

    • Mohsin2022's avatar
      Mohsin2022
      Icon for Helper I rankHelper I
      4 years ago

      How can i average price based on year criteria 

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

        AVERAGEX ( RELATEDTABLE, Price )