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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Conditional Sum

Hi, I have Sales Fact table by date.

on the report I have Total Sales by 'SaleType' as shown in the picture.

and what I need is if SaleType is "Online" , I want to add $0.5 to the Online "Total Sales" row and for all other Sale Types it will be only sum(SalesAmount).

 

ConditionalSum.PNG

1 ACCEPTED SOLUTION

Hi @Anonymous ,

OK used some slicers

Sales w Segment Channel Parters = 
var _CP = If(MAX(Segement[SegmentName])= "Channel Partners", SUM(SalesFact[ Sales])+5000,SUM(SalesFact[ Sales]))

return _CP

and added 5000 to Channel Partners. The logic is the same.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel



retail1.PNG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

13 REPLIES 13
Nathaniel_C
Super User
Super User

IF(SalesTypeName = "Online", [TotalSaleAmount] * 1.05, [TotalSalesAmount])

Hi @Anonymous ,

Try this.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

@Nathaniel_C : am getting below message to the Measure.

 

A single value for column 'SaleTypeName' in table 'SaleType' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Hi @Anonymous , are you trying to create a measure?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@Anonymous ,

I would go to Power Query and add a conditional column with the logic from above. 
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

@Nathaniel_C .

Here is the pbix file.

https://1drv.ms/u/s!Ai0GzdHCLgzPkn-A21nhmXwYmGtz?e=CUol3B

(ignore notes/comments in this file).

Thank you for looking into this. Really appreciate.

again I want this $0.5 adjustment to the total Online Sales.

another clarification. this 0.5 is not percentage. it is fixed Dollar amount. I just made up this number based on my scenario.

 

Hi @Anonymous ,

Sorry, this doesn't look the same?retail.PNG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Yes, @Nathaniel_C 

I just sent this file to play with it with some data.

based on this file data, requirement will be if country is Canada, add $1000 to the total.

 

Note: am using Power BI Report Server Sept 2019 version.

Hi @Anonymous ,

OK used some slicers

Sales w Segment Channel Parters = 
var _CP = If(MAX(Segement[SegmentName])= "Channel Partners", SUM(SalesFact[ Sales])+5000,SUM(SalesFact[ Sales]))

return _CP

and added 5000 to Channel Partners. The logic is the same.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel



retail1.PNG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Thank you, @Nathaniel_C 

It seems lieke Totals are not matching. 'Totals' in matrix must also 5000$ more.

Anonymous
Not applicable

Now totals are also matching. please find below video for Totals match from BI Elite .

 

https://www.youtube.com/watch?v=Ka7Ds4EAjNQ

 

Ok will look now





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Yes @Nathaniel_C . am trying to create a measure with this requirement and placing this measure on the visual.

 

I want this adjustment for total sum of "Online". 

If add a column it will add 0.5 to every row/transaction.

 

Hi @Anonymous ,

If you want to increase the total by .05 it will be the same either way 10 x10 = 100,
 100*1.05 = 105 or 10 x 10.5=105, yes?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.