topic Re: Generate rows at lower level in DAX Commands and Tips

Generate rows at lower level

pmargari — Sat, 14 Jan 2023 12:58:17 GMT

Hello all,
Any tips to generetate rows at lower level from a table with data on quarters , just to set the same value of each month related to his quarter ? (example below)
I have budgets for salesrep , at quater level, that I want to generate de same amount but at month level , any tips to make this append ?

Appreciate any tips
All the best
KR

Re: Generate rows at lower level

Nathaniel_C — Sat, 14 Jan 2023 20:41:59 GMT

Hi pmargari ,
You will not be able to make this append because the column headings are different. However you could add extra columns for each month based on the quarter values in Power Query. Go to the add column tab, Custom and name the columns by month, and insert the appropriate quarter.

Apply this table to Power BI and select the quarter, the month columns as in the picture.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Re: Generate rows at lower level

pmargari — Mon, 16 Jan 2023 09:14:26 GMT

Thansk for your reply Nathaniel_C ! , will try this option !!

Re: Generate rows at lower level

pmargari — Mon, 16 Jan 2023 09:22:40 GMT

Hello again
I think the way I picture my need have created a missunderstood , sorry for that , what I need is to create rows not columns as this example :

Thanks

Re: Generate rows at lower level

Nathaniel_C — Mon, 16 Jan 2023 17:34:39 GMT

Hello pmargari ,
Like this?

Color = IF(MAX(Sales[Month]) In{"Jan","Feb","Mar"},"yellow", "green") //Add this measure to use in your conditional formatting.

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsxJSi0qUdJRMjIAEsYgwhTKitWJVnLOSCzKSS2GSRmbAgkTEGFqCpZ3zEkrSk0BCYKkzUCEiQmEFRsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sales Rep" = _t, Q1 = _t, Q2 = _t, Q3 = _t, Q4 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales Rep", type text}, {"Q1", Int64.Type}, {"Q2", Int64.Type}, {"Q3", Int64.Type}, {"Q4", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Jan", each [Q1]), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Feb", each [Q1]), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Mar", each [Q1]), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Apr", each [Q2]), #"Added Custom4" = Table.AddColumn(#"Added Custom3", "May", each [Q2]), #"Added Custom5" = Table.AddColumn(#"Added Custom4", "June", each [Q2]), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom5",{{"Jan", Int64.Type}, {"Feb", Int64.Type}, {"Mar", Int64.Type}, {"Apr", Int64.Type}, {"May", Int64.Type}, {"June", Int64.Type}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Q1", "Q2", "Q3", "Q4"}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Sales Rep"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Month"}, {"Value", "Amount"}}), #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "Sort1", each if Text.Contains([Sales Rep], "t") then 1 else if Text.Contains([Sales Rep], "s") then 2 else if Text.Contains([Sales Rep], "d") then 3 else null), #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Sort2", each if [Month] = "Jan" then 1 else if [Month] = "Feb" then 2 else if [Month] = "Mar" then 3 else if [Month] = "Apr" then 4 else if [Month] = "May" then 5 else if [Month] = "June" then 6 else null), #"Changed Type2" = Table.TransformColumnTypes(#"Added Conditional Column1",{{"Sort1", type text}, {"Sort2", type text}}) in #"Changed Type2"

Above is the m language to build your table in Power Query. Then apply that table to Power BI, and add the measure above to conditional formatting.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel