Get data & calculate values in rows without applying UnPivot command

Question

Hi PBI Experts,I have a large &amp; 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.&nbsp;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 &amp; Europe" are fixed regions &amp; no scope of adding any more region.&nbsp;IN PUT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1ABCDEFGHIJ2CustomerProductCommission valueAmericasAfricaMiddle_EastIndiaAPACEuropeTotal%3Apple InciPhone10000030%0%10%10%20%30%100%4MicrosoftOffice36520000030%2%3%30%30%5%100%&nbsp;INPUT File in PBIX format is uploaded here --&gt; https://www.dropbox.com/s/9p1g58iox1tqx80/Calculate_SANS_Unpivot.pbix?dl=0&nbsp;And what I need is the following table as "Output"wherein Column "A &amp; B" is copied &amp; 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" --&gt;&nbsp;"Americas, Africa, Middle_East, India, APAC &amp; Europe" are fixed values &amp; 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.&nbsp;CustomerProductRegionCommission valueInput Table referenceApple InciPhoneAmericas&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 30,000-- &gt; C3* D 3Apple InciPhoneMiddle_East&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10,000-- &gt; C3* F 3Apple InciPhoneIndia&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10,000-- &gt; C3* G 3Apple InciPhoneAPAC&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20,000-- &gt; C3* H 3Apple InciPhoneEurope&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 30,000-- &gt; C3* I 3MicrosoftOffice365Americas&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 60,000-- &gt; C4* D 3MicrosoftOffice365Africa&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,000-- &gt; C4* E 3MicrosoftOffice365Middle_East&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6,000-- &gt; C4* F 3MicrosoftOffice365India&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 60,000-- &gt; C4* G 3MicrosoftOffice365APAC&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 60,000-- &gt; C4* H 3MicrosoftOffice365Europe&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10,000-- &gt; C4* I 3&nbsp;

mariusz · Answer

Hi&nbsp;Anonymous&nbsp;Please see the below M code that returns your desired&nbsp;result, you can copy it all and paste it in the newly created Blank Query's&nbsp;Advanced&nbsp;Editor.&nbsp;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.&nbsp;&nbsp;Best Regards,MariuszIf this post helps, then please consider Accepting it as the solution.Please feel free to connect with me.&nbsp;

anonymous · Answer

Hi Mariusz&nbsp;&nbsp;Can you please also upload the PBIX file with your MCode for me to understand better ?I am not a PBI expert :-)&nbsp;Thanks,Chandan.

mariusz · Answer

Hi&nbsp;Anonymous&nbsp;No problem see the below.https://drive.google.com/fi0le/d/1aPwdy0pWpnE0JbK_UzR_Jokju03iGkmO/view?usp=sharing&nbsp;let me know if you need anything else.&nbsp;Best Regards,MariuszPlease feel free to connect with me.&nbsp;

anonymous · Answer

Mariusz&nbsp;My colleague said that "Unpivot" has been done in the M Code that you shared + created a column for calculated value,&nbsp;I want to avoid that unpivot since my actual data is huge with many columns / rows &amp; I will end up performing many unpivots &amp; then that join function of multi talbes is too complex to perform &amp; I am unsucceful joining.&nbsp;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.&nbsp;Waiting for a solution.&nbsp;Thanks,Chandan

Forum Discussion

Get data & calculate values in rows without applying UnPivot command

4 Replies

Related content

Calculation not working when slicers applied

Apply Filter before Calculating Average

Group By not continuously applying

MoM calculation

Hot to apply Conditional Formatting to Calculation Groups and Calculation items

	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%

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