MEASURE: GET SUM FROM THE BOTTOM OF A TABLE UNTIL CONDITION IS REACHED

[aman749]

THERE IS A FILTERED TABLE AS FOLLOWING IN A VISUAL.

WHAT I NEED IS A MEASURE THAT GIVES SUM(QUANTITY*PRICE), STARTING FROM THE BOTTOM UNTIL QAUNTITY HAS REACHED TOTAL-QUANITY (175 IN THIS CASE).

SO IN THIS EXAMPLE THE MEASURE WILL BE LIKE THIS (20*232.50+10*217.60+50*216.50+95*216)=38171.  

 

ANY HELP FOR THIS MEASURE WILL BE MUCH APPRECIATED.

[Jihwan_Kim]

Hi, @aman749 

Please check the below picture and the sample pbix file's link down below.

All measures are in the sample pbix file, and all steps are numbered in front of each measure.

https://www.dropbox.com/s/dp8b83bqrrlsrzl/aman.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[aman749]

Hello @Jihwan_Kim 

First of all the measure that you have provided is what I want to calculate.

But This is only somewhat in the direction of what I need.

As the data that I have constitutes multiple categorical data. and can't have a fixed index column.

Here is the file with sample data.

https://1drv.ms/u/s!AnegLsk7hHY4ja8xJR1sv78y33j3Ng?e=adbBpz

Thanks for your help again.

Best regards

[Jihwan_Kim]

Hi, @aman749 

Thank you for your feedback.

The index column that I purposely created was for identifying the sequence. 

When I saw your sample pbix file, it has the date column. I think you can use this column similar to the index column. The only thing you need to define is the below.

The bottom = recent date?  or,

the bottom = oldest date?

Thanks.

[aman749]

The bottom you can define by sorting the data by column [RateTrn] ins ascending order as already done in the pbix file. Max([RateTrn]) is bottom.

[Jihwan_Kim · Accepted solution]

Hi, @aman749 

I am not 100% sure whether I understood your explanation correctly, but please check the link down below. All measures are in the sample pbix file.

https://www.dropbox.com/s/ig8zmvvej43ft3q/TEST%20DATA%20%281%29.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[aman749]

Hello @Jihwan_Kim 

Yes, the solution that you provided works perfectly.

Although I didn't fully understand everything that you did.

Thank you very much for your help.

Best regards