Conditional Sum

[Anonymous]

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).

[Nathaniel_C]

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

[Anonymous]

@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.

[Nathaniel_C]

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

[Nathaniel_C]

@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

[Anonymous]

@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.

[Nathaniel_C]

Hi @Anonymous ,

Sorry, this doesn't look the same?

[Anonymous]

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.

[Nathaniel_C · 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

[Anonymous]

Thank you, @Nathaniel_C 

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

[Anonymous]

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

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

[Nathaniel_C]

Ok will look now

[Anonymous]

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.

[Nathaniel_C]

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?