Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.