Level of detail calculation after splitting into rows

[axa830]

Hello All,

I have a problem that I have been stuck on for the last week. My data set is as under:

Account	Account Department	Account Site	Account Building	Year	Account Area	Account Affects	Sales
Acount1	Dept1	Site1	Building1	2021	Area1	a,b,c	100
Acount2	Dept2	Site2	Building2	2020	Area2	null	200
Acount3	Dept3	Site3	Building3	2021	Area3	a	2000
Acount4	Dept4	Site4	Building4	2020	Area4	a,b,c,d	2000
Acount5	Dept5	Site5	Building5	2021	Area5	d	2000
Acount6	Dept6	Site6	Building6	2021	Area6	null	2000
Acount7	Dept7	Site7	Building7	2020	Area7	null	2000

What I did was split the column Account Affects into multiple rows by using transform and splitting into rows to get the resulting data set:

Account	Account Department	Account Site	Account Building	Year	Account Area	Account Affects	Sales
Acount1	Dept1	Site1	Building1	2021	Area1	a	100
Acount1	Dept1	Site1	Building1	2021	Area1	b	100
Acount1	Dept1	Site1	Building1	2021	Area1	c	100
Acount2	Dept2	Site2	Building2	2020	Area2	null	200
Acount3	Dept3	Site3	Building3	2021	Area3	a	2000
Acount4	Dept4	Site4	Building4	2020	Area4	a	2000
Acount4	Dept4	Site4	Building4	2020	Area4	b	2000
Acount4	Dept4	Site4	Building4	2020	Area4	c	2000
Acount4	Dept4	Site4	Building4	2020	Area4	d	2000
Acount5	Dept5	Site5	Building5	2021	Area5	d	2000
Acount6	Dept6	Site6	Building6	2021	Area6	null	2000
Acount7	Dept7	Site7	Building7	2020	Area7	null	2000

Now I am stuck in a problem as I have card and other visuals that need to be prepared. What I want to achieve is the total of sales based on all the slicers(all columns in the dataset except Sales).

So suppose if I have no filter selected my total would become (10,300 by summing 100 from Account1,200 from Account2 and so on) i.e take the total of the three rows that account 1 splits into and then divide by the count distinct of the Account Affects column which in this case would be 3(300/3=100) and then move on to the next Account and apply same logic.

But if from a slicer I select Account Afffects "a" then my total should be 5100(100 from Account1,2000 from Account3,and 2000 from Account4 for a total of 5100)

I have tried couple of different approaches but cant seem to find a solution. Please advise.

[AlB · Accepted solution]

Hi @axa830 

Why don't you do that division by the number of rows directly in power query? It will be easier to operate in DAX later. Somethig like this. Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdA7CsMwEATQu6hWIetbO+QGKY0LxxZBIJQQrPvHa49hlaQajYrH7g6D6OdnLWsnpLjG1563tEbKS015SeVBb600Rf+OE+Uk73LeslNKjPJENBANRDNEH4gCQrXUnPdfbhgYBoZhhmkHoTodABcsBAvBMsG2U9hzFbn8Og6Og+OY49pJqP4RPAQPwTPBt4Jv7sGRACQACQwJ7TrhGxk/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Account = _t, #"Account Department" = _t, #"Account Site" = _t, #"Account Building" = _t, Year = _t, #"Account Area" = _t, #"Account Affects" = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account", type text}, {"Account Department", type text}, {"Account Site", type text}, {"Account Building", type text}, {"Year", Int64.Type}, {"Account Area", type text}, {"Account Affects", type text}, {"Sales", Int64.Type}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Account Affects],",")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each [Sales]/List.Count([Custom]), type number),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom1", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Account Affects", "Sales"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Account Affects"}, {"Custom.1", "Sales"}})
in
    #"Renamed Columns"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers