Get data & calculate values in rows without applying UnPivot command

[Anonymous]

Hi PBI Experts,

I have a large & complex data set which is cumbersome to UnPivot.

Why no UnPivot - I tried splitting the table to unpivot, then Unpivot them and later join them all, however, not getting correct values in Joined table.

The following first table is "INPUT" table (simplfied table for this question, but I have amany columns which I need in Output).

Please note that : - Region "Americas, Africa, Middle_East, India, APAC & Europe" are fixed regions & no scope of adding any more region.

	IN PUT
1	A	B	C	D	E	F	G	H	I	J
2	Customer	Product	Commission value	Americas	Africa	Middle_East	India	APAC	Europe	Total%
3	Apple Inc	iPhone	100000	30%	0%	10%	10%	20%	30%	100%
4	Microsoft	Office365	200000	30%	2%	3%	30%	30%	5%	100%

INPUT File in PBIX format is uploaded here --> https://www.dropbox.com/s/9p1g58iox1tqx80/Calculate_SANS_Unpivot.pbix?dl=0

And what I need is the following table as "Output"

wherein Column "A & B" is copied & then Region names are populated in rows + commission value is calculated with their respective % (the last column is just the input table cell reference showing how the regional commission value was calculated. Als in this example for Apple Inc, "Africa" region is missing because Input table has "Zero %" in cell E4.

As I mentioned earlier that the "Region" --> "Americas, Africa, Middle_East, India, APAC & Europe" are fixed values & no scope of adding any more region - So I can create a table of "Region" but I don't know of any option / DAX function to get calculated following Output without "UNPIVOT" perfomed.

Customer	Product	Region	Commission value	Input Table reference
Apple Inc	iPhone	Americas	30,000	-- > C3* D 3
Apple Inc	iPhone	Middle_East	10,000	-- > C3* F 3
Apple Inc	iPhone	India	10,000	-- > C3* G 3
Apple Inc	iPhone	APAC	20,000	-- > C3* H 3
Apple Inc	iPhone	Europe	30,000	-- > C3* I 3
Microsoft	Office365	Americas	60,000	-- > C4* D 3
Microsoft	Office365	Africa	4,000	-- > C4* E 3
Microsoft	Office365	Middle_East	6,000	-- > C4* F 3
Microsoft	Office365	India	60,000	-- > C4* G 3
Microsoft	Office365	APAC	60,000	-- > C4* H 3
Microsoft	Office365	Europe	10,000	-- > C4* I 3

[Anonymous]

@Mariusz 

My colleague said that "Unpivot" has been done in the M Code that you shared + created a column for calculated value,

I want to avoid that unpivot since my actual data is huge with many columns / rows & I will end up performing many unpivots & then that join function of multi talbes is too complex to perform & I am unsucceful joining.

What I need is that I have a fixed "region" values as of now and that PBI does the commission calculation in same output format as if UnPivot was performed.

Waiting for a solution.

Thanks,

Chandan

[Mariusz]

Hi @Anonymous 

Please see the below M code that returns your desired result, you can copy it all and paste it in the newly created Blank Query's Advanced Editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyElV8MxLVtJRygzIyM9LBTIMDUAAyDA20DMwUAUyYLQhBsMIxjBGSAGpWJ1oJd/M5KL84vy0EqCgf1paZnKqsZkpWAu68UYwM9DNgjNMUQyPBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Customer = _t, Product = _t, #"Commission value" = _t, Americas = _t, Africa = _t, Middle_East = _t, India = _t, APAC = _t, Europe = _t, #"Total%" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Product", type text}, {"Commission value", Int64.Type}, {"Americas", Percentage.Type}, {"Africa", Percentage.Type}, {"Middle_East", Percentage.Type}, {"India", Percentage.Type}, {"APAC", Percentage.Type}, {"Europe", Percentage.Type}, {"Total%", Percentage.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Customer", "Product", "Commission value", "Total%"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each [Value] * [Commission value]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}})
in
    #"Changed Type1"

Let me know if it worked for you. 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Anonymous]

Hi @Mariusz 

Can you please also upload the PBIX file with your MCode for me to understand better ?

I am not a PBI expert 🙂

Thanks,

Chandan.

[Mariusz]

Hi @Anonymous 

No problem see the below.

https://drive.google.com/fi0le/d/1aPwdy0pWpnE0JbK_UzR_Jokju03iGkmO/view?usp=sharing

let me know if you need anything else.

Best Regards,
Mariusz

Please feel free to connect with me.