Sum values that contain identical row values based on values in another column

[Anonymous]

Hello power bi community! Long time reader, first time caller.

I am hoping it's possible to construct a measure that can sum values that don't (or do) contain certain values in another column, where a column contains identical values (in this case the transaction number). For example, i have the following data:

Store	Transaction no	product type	sales $
1	123	merch	700
1	123	warranty	150
1	123	install	150
1	123	merch	500
2	124	merch	600
3	125	merch	450
2	126	merch	600

and then i use a matrix to sum that data like so:

Store	Transaction no	sum of sales $
1	123	1500
2	124	600
2	126	600
3	125	450

What I’m hoping to get is a measure i can put in the matrix, similar to the following, where i can sum the data that contains merch and a service (IE: warranty or install), where the transaction number is the same ("service sales"), and also that perhaps may not have any services attached ("merch only sales"). In my case, sometimes merch will be sold on its own, and sometimes with either a warranty or an install, or both.

Store	Transaction no	sum of sales $	merch only sales	service sales
1	123	1500	0	1500
2	124	600	600	0
2	126	600	600	0
3	125	450	450	0

I am also open to prepping the data with some M in power query, but any suggestions are appreciated.

Thanks!

[dax]

Hi @Anonymous , 

You also could use below Mcode to achieve your goal.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MgaSualFyRlA2tzAQClWB1mmPLGoKDGvpBIkYIoumZlXXJKYk4NVDmakKdRII7CMCZKMGVTGGCxjiiRjYoqsxwxdTywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Store = _t, #"Transaction no" = _t, #"product type" = _t, #"sales $" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Store", Int64.Type}, {"Transaction no", Int64.Type}, {"product type", type text}, {"sales $", Int64.Type}}),
    #"Grouped Rows1" = Table.Group(#"Changed Type", {"Store", "Transaction no"}, {{"Count", each  Text.Combine([product type], ","), type number}, {"aa", each _, type table [Store=number, Transaction no=number, product type=text, #"sales $"=number]}}),
    Custom1 = Table.ReplaceValue(#"Grouped Rows1", each [Count], each if Text.Contains([Count], "install") or Text.Contains([Count], "warranty") then "service" else "merch only", Replacer.ReplaceValue,{"Count"}),
    #"Expanded aa" = Table.ExpandTableColumn(Custom1, "aa", {"product type", "sales $"}, {"product type", "sales $"})
in
    #"Expanded aa"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

Refer : https://www.dropbox.com/s/zspy30le149lvwb/identicalsales.pbix?dl=0

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[az38]

Hi @Anonymous 

try measures:

sum of sales $ = calculate(sum([sales $]);ALLEXCEPT('Table';'Table'[Store];'Table'[Transaction no]))

then

service sales = 
var _isMerch = calculate(distinctcount('Table'[product type]);ALLEXCEPT('Table';'Table'[Store];'Table'[Transaction no]);'Table'[product type]<>"Merch")
return 
if(_isMerch>0;[sum of sales $];0)

then

merch only sales = [sum of sales $]-[service sales]

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn