cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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.

 SuperMarket Goods Purchased Jan21 Quantity Jan Expenditure Feb21 Quantity Feb Expenditure Mar Quantity... A Furniture 1 \$300 2 \$400 ... A Groceries 8 \$80 13 \$110 ... A Spices 5 \$50 4 \$30 ... B Furniture 1 \$250 1 \$260 ... B Groceries 12 \$110 15 \$130 ... B Spices 2 \$15 3 \$25 ...

What I would like to obtain would be:

 Period A_Furniture B_Furniture A_Groceries B_Groceries A_Spices B_Spices Total Quantity Total Expenditure % Change Jan21 \$300 \$250 \$80 \$110 \$50 \$15 29 \$805 - Feb21 \$400 \$260 \$110 \$130 \$30 \$25 38 \$995 23.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
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

6 REPLIES 6
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.

Helper I

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 🙂

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

Helper I

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.

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

Helper I

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

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors