Skip to main content
cancel
Showing results for 
Search instead 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

Reply
rpinxt
Impactful Individual
Impactful Individual

Only count/sum Quantity of last log date

So I have this table output:

rpinxt_0-1690368866685.png

So I have a delivery with multiple materials.

But some of these materials (see for example the yellow) have 2 log dates.

The correct Quantity for  this material would be 2 and not 4 (when I would take out the date)

 

Made QCorr like :

QCorr = CALCULATE([Quantity],Test[LogDate] = Test[MaxLog])
Actually expected this would work. No clue why it still shows 2 where logdate is 18 and maxlog is 19.
 
But this is not doing what I want.
In the end I want to take the dates out and that material should show 2.
Because the total should be 102 not 122.
122 is because all of the doubles in this delivery.
 
How can I make it 

 

1 ACCEPTED SOLUTION

Opsss... I copied badly the measure:

 

 

QCorr = SUMX(VALUES('Table'[Material]),[sumSc])

mlsx4_0-1690372996662.png

 

 

View solution in original post

6 REPLIES 6
rpinxt
Impactful Individual
Impactful Individual

@mlsx4 
unfortunately not:

rpinxt_0-1690369510171.png

 

ps: maxlog is a calculated column

Hi @rpinxt 

 

I have created an auxiliary sum to get the maximum value in case of several rows:

 

 

sumSc = CALCULATE(MAX('Table'[Quantity]), FILTER(ALLEXCEPT('Table','Table'[Material]), 'Table'[LogDate]=MAX('Table'[LogDate])))

 

 

 And now, in QCorr:

 

 

 

QCorr = SUMX(VALUES('Table'[Material]),IF(DISTINCTCOUNT('Table'[Material])>1,[sumSc],SUM('Table'[Quantity])))

 

 

You don't need any "MaxLog" column

 

mlsx4_0-1690371613350.png

 

rpinxt
Impactful Individual
Impactful Individual

Thanks @mlsx4 but look on the second row....it says 4 and should be 2.

 

Yes your total of 102 is correct but if you sum all the line separate you will again get 122.

Opsss... I copied badly the measure:

 

 

QCorr = SUMX(VALUES('Table'[Material]),[sumSc])

mlsx4_0-1690372996662.png

 

 

rpinxt
Impactful Individual
Impactful Individual

Yes thanks @mlsx4 this seems to work.
But you need to remove the date colunns.

 

This is part of a bigger thing.

I have more countries, deliveries and months etc.

 

I should all go into a stacked column chart.

With periods on the x-axis where I link a date field (maxlog) to a date table (auto).

Wondering if it will give the correct output.

But will test. Thanks for this!

mlsx4
Super User
Super User

I think you shouldn't include those columns in the table or they will be added to the sum. If you just leave delivery, material and qcorr, it should work.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors