Forum Discussion

Gusdate2's avatar
Gusdate2
Helper I
4 years ago
Solved

Power BI Structure for Visual

Thank You for looking... Link to Example   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 current...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    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

AntonioM's avatar
AntonioM
Solution Sage
4 years ago

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

  • Gusdate2's avatar
    Gusdate2
    Helper I
    4 years ago

    Link to Example

    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

     

    • v-easonf-msft's avatar
      v-easonf-msft
      Community Support
      4 years ago

      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