Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lwklwk98
Helper I
Helper I

DAX Help

Hello! I am a total beginner in PowerBI and I am stuck on this issue for a day and I really cannot figure this out and I would really need your help! 

 

This is quite an elementary problem and probably it has been answered before but I have been searching and I cannot find anything and here I am. Any help will be greatly appreciated!

 

I have this dataset (Table1): Each Row represents a trip to the supermarket.

SuperMarketGoods PurchasedJan21 QuantityJan ExpenditureFeb21 QuantityFeb ExpenditureMar Quantity...
AFurniture1$3002$400...
AGroceries8$8013$110...
ASpices5$504$30...
BFurniture1$2501$260...
BGroceries12$11015$130...
BSpices2$153$25...

 

What I would like to obtain would be: 

 

PeriodA_FurnitureB_FurnitureA_GroceriesB_GroceriesA_SpicesB_SpicesTotal QuantityTotal Expenditure% Change
Jan21$300$250$80$110$50$1529$805-
Feb21$400$260$110$130$30$2538$99523.6%
Mar21...........................
Apr21..............................

 

My attempt so far yields me the column for Total Quantity and Expenditure using a DAX as follows:
TableName =

Union( 

           Row ( "Period", "2021-01", "Total Quantity", Sum( Table1[Jan21 Quantity] ), "Total Expenditure", Sum( Table1[Jan21 Expenditure] ) ),
           Row ( ....
)

Is there an easier way to do this and how do I write a DAX to filter the different types of Good Purchased and for the % change? 

Thank you so much!

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

I'll post the code (which you can place in the Advanced Editor in Power Query).  This'll either confound you or inspire you.  Let's hope it's the latter.

Basically you'll be unpivotting the column pairs.  To do this you need consistency in the naming of pairs e.g. Jan21 for 2 columns or Jan for 2 columns.

Here's the code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY0xDoAgDEXvQhwZLIhx1UEP4EiYCAOLGpT729KoDA5NX/Jf/rdWjEKKOactXjkFZMBrdNviU4QdopPsLWn3IcVwIg8UDqSBJgT4vPWIvkiGEkNSx7VFmf4mVdEY+8+rJ0E9Q8ilGqrGd5QtyjUXC+du", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SuperMarket = _t, #"Goods Purchased" = _t, #"Jan21 Quantity" = _t, #"Jan Expenditure" = _t, #"Feb21 Quantity" = _t, #"Feb Expenditure" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SuperMarket", type text}, {"Goods Purchased", type text}, {"Jan21 Quantity", Int64.Type}, {"Jan Expenditure", type text}, {"Feb21 Quantity", Int64.Type}, {"Feb Expenditure", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"SuperMarket", "Goods Purchased"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1","21","",Replacer.ReplaceText,{"Attribute.1"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Attribute.2]), "Attribute.2", "Value"),
    #"Replaced Value1" = Table.ReplaceValue(#"Pivoted Column","$","",Replacer.ReplaceText,{"Expenditure"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value1",{{"Expenditure", Int64.Type}, {"Quantity", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Attribute.1", "Period"}})
in
    #"Renamed Columns"

Looks complex but it was all done with the mouse + interface.

 

See if you can apply it your data.  I won't be around for a few hours but I encourage you to work it out and post back later if you're struggling.

There's lots of forum examples of unpivotting pairs.  Let me know how it goes

View solution in original post

6 REPLIES 6
HotChilli
Super User
Super User

Well, it looks like your real data is a more complex situation.

Here are my suggestions:

1. Using the first 6 columns from the sample data you gave, apply the code I provided.  See if you can make it work.  Look at each step to see what's going on.  Understand the strategy (basically reshaping the data by selecting 2 fixed columns and unpivotting the pairs)  and see how each step gets you there. 

Reshaping the data in Power Query allows you to start to get data that powerbi likes i.e. columnar and from there you can start to investigate star schemas.

2. Close and Apply Power Query.  See if you can create a matrix visual with the reshaped data.  If that works, go to 3.

----------------------

3.  With your real data, start a prototype by getting rid of 90% of the columns.  Make it simple.  If the data is like the sample you provided, you're on a good start.  If you now realise that you've asked the wrong question then repost with new sample data and requirements (you'll be closer to a solution and you've learnt a lot)

4. I don't think that sqlbi article is a good place for you to start - it's an advanced technique and solves a specific issue

5. It's highly unlikely that you're going to find a web page with "The solution" - try to understand your data and what your desired outcomes are.

Good luck.

Hey!

 

It took me a while for me to follow your steps but I managed to get the desired result I wanted. I reduced the number of categories to 2 and it really helped. 

 

Really appreciate your effort and time to help me out! Cheers Mate 🙂

HotChilli
Super User
Super User

I'll post the code (which you can place in the Advanced Editor in Power Query).  This'll either confound you or inspire you.  Let's hope it's the latter.

Basically you'll be unpivotting the column pairs.  To do this you need consistency in the naming of pairs e.g. Jan21 for 2 columns or Jan for 2 columns.

Here's the code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY0xDoAgDEXvQhwZLIhx1UEP4EiYCAOLGpT729KoDA5NX/Jf/rdWjEKKOactXjkFZMBrdNviU4QdopPsLWn3IcVwIg8UDqSBJgT4vPWIvkiGEkNSx7VFmf4mVdEY+8+rJ0E9Q8ilGqrGd5QtyjUXC+du", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SuperMarket = _t, #"Goods Purchased" = _t, #"Jan21 Quantity" = _t, #"Jan Expenditure" = _t, #"Feb21 Quantity" = _t, #"Feb Expenditure" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SuperMarket", type text}, {"Goods Purchased", type text}, {"Jan21 Quantity", Int64.Type}, {"Jan Expenditure", type text}, {"Feb21 Quantity", Int64.Type}, {"Feb Expenditure", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"SuperMarket", "Goods Purchased"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1","21","",Replacer.ReplaceText,{"Attribute.1"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Attribute.2]), "Attribute.2", "Value"),
    #"Replaced Value1" = Table.ReplaceValue(#"Pivoted Column","$","",Replacer.ReplaceText,{"Expenditure"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value1",{{"Expenditure", Int64.Type}, {"Quantity", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Attribute.1", "Period"}})
in
    #"Renamed Columns"

Looks complex but it was all done with the mouse + interface.

 

See if you can apply it your data.  I won't be around for a few hours but I encourage you to work it out and post back later if you're struggling.

There's lots of forum examples of unpivotting pairs.  Let me know how it goes

I tried to unpivot my whole table but I do not have a fixed column that I can use as a pivot (Many examples online use time but Im pivoting the time(monthly) column as well)
My apologies but I am still very new and the whole process isn't intuitive for my dumb brain. 

Another problem I faced was with the insane amount of categorizations (this is a mock-up data, the actual one has like 4-5 category columns and each has different categories like geolocation, supermarket names, various goods, etc). If you include each of the month's data, it went all the way to 100 over columns since I have >5 years of data. 

For analysis purposes, I believe what I want to achieve is similar to this:
https://www.sqlbi.com/articles/filtering-and-comparing-different-time-periods-with-power-bi/
where I can make comparisons between different categories relative to time.

 

HotChilli
Super User
Super User

Does it have to be DAX?  It would be easier in Power Query to reshape data and then use a matrix to display

Right... It doesn't have to be DAX. I was too fixated on that 😅 
Do you have any suggestions on how to do that? 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.