Forum Discussion
Power BI Structure for Visual
Thank You for looking...
So I have been asked to add a page to a dashboard that is already being pushed out.
On this page they want a visual that presents content that is currently part of a monthly PowerPoint summary...on the PowerPoint visual they are clearly just entering numbers into a table.
The part I am struggling with is that they have different formatting for the different rows of the table...some currency, some percent, some whole number and some decimal.
So I know how I would handle it in excel...I have provided an example linked above. Each month I would simply go down the line and add the values.
But here is where I am struggling, I can't even think of the best way to organize the data for this, let alone building the visual. My head just isn't working right now.
Sadly the only thing I can think of is re-creating a manual table each month and re-creating the visual off it each month. Not a crap ton of work...it just feels wrong.
Any insight, guidence or ideas are absolutely welcome. Thank you again for looking...
-John
Hi, Gusdate2
You might consider transposing this table in PowerBI, which makes it easy to format the data by columns without DAX.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jVPbasMwDP2VENo3ISzL1+ftddBd2EvpQ9nC6EgvdG1Z/36WbboN1iwQzsmxoyM5lubzdrbfvncvhxZaS84kIqIgpEkLWVJCrGXv72cB8/Z52x/XXVKNB1YBtDGNKALnHJC6KCLQylcVYwTikJUGxQQ+2mYw0eOu27zK9xOCwApUZFTFYcJBg1EBbSzasAbmiKSLtsoCBUau++lsQI4whn9yLk+rzdtHyWo8WHZI1YM9eEuouEgdIHqP8ZIRvHdo6q7UywoDDee72a53ffe5OpwpX4hchJZX7QR8Ag4jHfIlphpyTExo2ApGWTHWjbThtFTbQ0DCSNxobB05liVES0lGIEseW4LNR5Bu9GIWRZPyGYeb87brV6duf35YHrqy7DX6qXBqnmnR8ae+7nV/XPapmObbyzGaaeFYOXs785/X010RZcR+w/Wo2ewSR4R1VrEOK9ZpxYE/svgC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, YTD = _t, Jan = _t, Feb = _t, Mar = _t, Apr = _t, May = _t, Jun = _t, Jul = _t, Aug = _t, Sep = _t, Oct = _t, Nov = _t, Dec = _t]), #"Demoted Headers" = Table.DemoteHeaders(Source), #"Transposed Table" = Table.Transpose(#"Demoted Headers"), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Project", Int64.Type}, {"Volume", Int64.Type}, {"Spend", Currency.Type}, {"Savings", Currency.Type}, {"Complexity1", Int64.Type}, {"Complexity2", Int64.Type}, {"Complexity3", Int64.Type}, {"Complexity4", Int64.Type}, {"Complexity5", Int64.Type}, {"DeliveryRate", Percentage.Type}, {"Quality Rate", Percentage.Type}, {"TM", Int64.Type}, {"PPTM", type number}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Month"}}), #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1, Int64.Type) in #"Added Index"Best Regards,
Community Support Team _ Eason
3 Replies
If you have a measure that gives you the values you want, you could use SWITCH to format the different rows in the different ways. SWITCH and SELECTEDVALUE will let the measure know what row of the table it's currently on and give the FORMAT function a different format string for each one.
FORMAT ( [Measure], SWITCH ( SELECTEDVALUE( Table[row header] ), "row1", "00#", "row2", "Percent", "row3", "Currency", "General Number" ) )All the different FORMAT strings should be on FORMAT function (DAX) - DAX | Microsoft Docs
Thank you for the reply. it sounds logical, the one question I have is where do I use the formula?
I put together an example where I have the data input into a table and then a measure for the value, I just don't know where to apply teh formula to the Visual...can't say I have ever done that before.
Any insight would be appreciated. Thank You
Hi, Gusdate2
You might consider transposing this table in PowerBI, which makes it easy to format the data by columns without DAX.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jVPbasMwDP2VENo3ISzL1+ftddBd2EvpQ9nC6EgvdG1Z/36WbboN1iwQzsmxoyM5lubzdrbfvncvhxZaS84kIqIgpEkLWVJCrGXv72cB8/Z52x/XXVKNB1YBtDGNKALnHJC6KCLQylcVYwTikJUGxQQ+2mYw0eOu27zK9xOCwApUZFTFYcJBg1EBbSzasAbmiKSLtsoCBUau++lsQI4whn9yLk+rzdtHyWo8WHZI1YM9eEuouEgdIHqP8ZIRvHdo6q7UywoDDee72a53ffe5OpwpX4hchJZX7QR8Ag4jHfIlphpyTExo2ApGWTHWjbThtFTbQ0DCSNxobB05liVES0lGIEseW4LNR5Bu9GIWRZPyGYeb87brV6duf35YHrqy7DX6qXBqnmnR8ae+7nV/XPapmObbyzGaaeFYOXs785/X010RZcR+w/Wo2ewSR4R1VrEOK9ZpxYE/svgC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, YTD = _t, Jan = _t, Feb = _t, Mar = _t, Apr = _t, May = _t, Jun = _t, Jul = _t, Aug = _t, Sep = _t, Oct = _t, Nov = _t, Dec = _t]), #"Demoted Headers" = Table.DemoteHeaders(Source), #"Transposed Table" = Table.Transpose(#"Demoted Headers"), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Project", Int64.Type}, {"Volume", Int64.Type}, {"Spend", Currency.Type}, {"Savings", Currency.Type}, {"Complexity1", Int64.Type}, {"Complexity2", Int64.Type}, {"Complexity3", Int64.Type}, {"Complexity4", Int64.Type}, {"Complexity5", Int64.Type}, {"DeliveryRate", Percentage.Type}, {"Quality Rate", Percentage.Type}, {"TM", Int64.Type}, {"PPTM", type number}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Month"}}), #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1, Int64.Type) in #"Added Index"Best Regards,
Community Support Team _ Eason
