cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## 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

2 ACCEPTED SOLUTIONS
Super User

@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] )``````
Super User

@Mohsin2022

``````BOMID New =
CALCULATE ( MAX ( Data[BOMID] ), ALLEXCEPT ( Data, Data[ProdID] ) )``````
23 REPLIES 23
Helper I

for Example

Super User

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

Helper I

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

Super User

Yes

Helper I

How can i average price based on year criteria

Super User

AVERAGEX ( RELATEDTABLE, Price )

Helper I

Brother its not working getting one value in all feilds

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

Helper I

Helper I

@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

Super User

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

Helper I

i used look up and first non blank with filter

Helper I

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

Super User

@Mohsin2022

``````BOMID New =
CALCULATE ( MAX ( Data[BOMID] ), ALLEXCEPT ( Data, Data[ProdID] ) )``````
Helper I
 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

Helper I

@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

Helper I

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

Super User

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

Helper I

@tamerj1  its part of source data from sql query

Super User

@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] )``````
Helper I

@tamerj1  THANKS DEAR ITS WORKING

Announcements