cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ThomasWeppler
Resolver I
Resolver I

Combining max and if

Hi Power BI community

I have a feeling that this question has a somewhat simple answer but I just cannot figure it out so I hope someone here can help me.
I want to write a function that look at the amount and divide it by a 100 
E. udgifter = divide(amount,100)
but if multiple rows with the same todoid and budgetpostid I only want to include the last of them with is the one with the highest id number and set the amout to the other ones to zero.
I think the solution is to combine max and if function so it only look at the highest id, but only have it apply if a row has both the same todoid and budgetpostid.

I have added a screenshot and I just want the row with id = 1159 ti have E. udgifter = 0, because another amount was posted later.

maxandif.png

 

I hope someone here can help me on my way.

1 ACCEPTED SOLUTION
ddpl
Solution Supplier
Solution Supplier

Hey I have solution in Power Query,

 

Step 1: Merge the column todoid and budgetpostid, named as g_b

 

Step 2: Group by the column g_b

 

bin3.png

 

 

 

 

 

 

 

 

 

 

 

 

Step 3: Expand the rest table

 

Step 4: Add Custom Column

amount_temp = if [id_temp] = [id] then [amount] else 0

 

Step 5: Add Custom Column

E. udgifter = [amount_temp]/100

 

remove unnecessary columns and that's it.

 

I hope It will work for you.

View solution in original post

2 REPLIES 2
ddpl
Solution Supplier
Solution Supplier

Hey I have solution in Power Query,

 

Step 1: Merge the column todoid and budgetpostid, named as g_b

 

Step 2: Group by the column g_b

 

bin3.png

 

 

 

 

 

 

 

 

 

 

 

 

Step 3: Expand the rest table

 

Step 4: Add Custom Column

amount_temp = if [id_temp] = [id] then [amount] else 0

 

Step 5: Add Custom Column

E. udgifter = [amount_temp]/100

 

remove unnecessary columns and that's it.

 

I hope It will work for you.

Thanks a lot

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors