SUM RESULT SHOULD BE SHOWN AGAINST ONE ROW

[Mohsin2022]

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 

[tamerj1 · Accepted solution]

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

[Mohsin2022]

for Example

[tamerj1]

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]

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]

Yes

[Mohsin2022]

How can i average price based on year criteria 

[tamerj1]

AVERAGEX ( RELATEDTABLE, Price )

[Mohsin2022]

Brother its not working getting one value in all feilds

i have attached one picture below i want to do same think 

[Mohsin2022]

@tamerj1  please help 

[Mohsin2022]

@tamerj1  thanks for your support 

one more thing 

i have two table i need to look up price from one table for items both table have multiple items for each items but as per your last trick i have used index column and created same formula for last price now i have last price column where only last prices are shown rest lines are empty now i need to lookup from that last price into other column when i used lookup it shows error of multiple entries supplied

please help

[tamerj1]

@Mohsin2022 
What was the formula that you've used?

[Mohsin2022]

i used look up and first non blank with filter

[Mohsin2022]

I have one more issue

in my data some of BOMID column has no value i want to make one criteria that if BOMID is blank then give me same BOMID of that PRODID

[tamerj1 · Accepted solution]

@Mohsin2022 
Please use

BOMID New =
CALCULATE ( MAX ( Data[BOMID] ), ALLEXCEPT ( Data, Data[ProdID] ) )

[Mohsin2022]

ProdID	Current	Required
RAK-001	250	250
RAK-001	250
RAK-002	300	300
RAK-002	300

for better undertsanding i have created sample table 

[Mohsin2022]

@tamerj1 when there is change in PRODID column on the very first record sum value should be displayed rather than on all line with same value 

i have data of around 100k rows

[Mohsin2022]

@tamerj1  in the right last column i just need only one value now each row has same values

[tamerj1]

@Mohsin2022 
Ok that is clear but how did you calculate that column? In Power Query? Or this is part of the source data?

[Mohsin2022]

@tamerj1  its part of source data from sql query

[tamerj1 · Accepted solution]

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

[Mohsin2022]

@tamerj1  THANKS DEAR ITS WORKING