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
cristianj
Helper IV
Helper IV

how can i select top records from a table having their sum equal to a value

hello, 

i have to select top records , the structure is simple, id, date and value, and for each date, i have to get the first records for which the sum of value = 0.7* total value for each day something like this

 

         
Data        
id dayvalue      
id101.01.2000   10      
id201.01.2000   4      
id301.01.2000   5   Temp  
id401.01.2000   7      
id501.01.2000   5    TotalTotal 70%
id601.01.2000   8   01.01.200039   27.3
id701.02.2000   12   01.02.200042   29.4
id801.02.2000   9   01.03.200035   24.5
id901.02.2000   6      
id1001.02.2000   8      
id1101.02.2000   4      
id1201.02.2000   3      
id1301.03.2000  11      
id1401.03.2000   3      
id1501.03.2000   3      
id1601.03.2000   7      
id1701.03.2000   9      
id1801.03.2000   2      
         
RESULT        
iddayvalue      
id101.01.2000   10      
id601.01.2000   8      
id401.01.2000   7      
id701.02.2000   12      
id801.02.2000   9      
id1001.02.2000    8      
id1301.03.2000   11      
id1701.03.2000   9      

 

Thank you

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

You may download my revised PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

9 REPLIES 9
Ashish_Mathur
Super User
Super User

You may download my revised PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you verry much!

 

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

thank you very much, can you help me change it so it orders by values not by id? i made a mistake when i write the example. thank you

 

You are welcome.  You have completely changed the example.  Show the expected result very clearly.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

i've change it. thank you, it is possible to make a new table having the result structure?

 

lbendlin
Super User
Super User

You will need to specify the sort order for getting to the 70%.  The usual order for a histogram is by value descending.  See attached for an implementation example

 

lbendlin_0-1664067933839.png

 

Hello, you were right , i need the sort order to be values descending, i have a small problema beacuse i don't understand how it calculate the day cumul, when o apply the example to my data, it result something like this 

cristianj_0-1664181546066.png

and i don;t understand from where it gets the value for iphone 13 pro, pro max...

the formula is 

Val **bleep** ziln =
var mxt=max('buy_phone_details'[Buy Price €])
return calculate([Total zi],buy_phone_details[Buy Price €]>=mxt)
thank you
 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors