cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Chandan Regular Visitor
Regular Visitor

Get data & calculate values in rows without applying UnPivot command

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         
1ABCDEFGHIJ
2CustomerProductCommission valueAmericasAfricaMiddle_EastIndiaAPACEuropeTotal%
3Apple InciPhone10000030%0%10%10%20%30%100%
4MicrosoftOffice36520000030%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.

 

CustomerProductRegionCommission valueInput Table reference
Apple InciPhoneAmericas                           30,000-- > C3* D 3
Apple InciPhoneMiddle_East                           10,000-- > C3* F 3
Apple InciPhoneIndia                           10,000-- > C3* G 3
Apple InciPhoneAPAC                           20,000-- > C3* H 3
Apple InciPhoneEurope                           30,000-- > C3* I 3
MicrosoftOffice365Americas                           60,000-- > C4* D 3
MicrosoftOffice365Africa                             4,000-- > C4* E 3
MicrosoftOffice365Middle_East                             6,000-- > C4* F 3
MicrosoftOffice365India                           60,000-- > C4* G 3
MicrosoftOffice365APAC                           60,000-- > C4* H 3
MicrosoftOffice365Europe                           10,000-- > C4* I 3

 

4 REPLIES 4
Super User
Super User

Re: Get data & calculate values in rows without applying UnPivot command

Hi @Chandan 

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.
Mariusz Repczynski

 

Chandan Regular Visitor
Regular Visitor

Re: Get data & calculate values in rows without applying UnPivot command

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.

Super User
Super User

Re: Get data & calculate values in rows without applying UnPivot command

Hi @Chandan 

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.
Mariusz Repczynski

 

Chandan Regular Visitor
Regular Visitor

Re: Get data & calculate values in rows without applying UnPivot command

@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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 270 members 2,171 guests
Please welcome our newest community members: