Forum Discussion

techsheep's avatar
techsheep
New Member
1 year ago
Solved

How to format table in Power BI based on source data?

Hi, how can I get a table in Power BI to source from my imported Excel data and mimic what I have in Excel like this?: Row 2 is referencing the sum of each Game in the Data Source tab. Row 3 ...
Need Help
Tutorial Requests
  • shafiz_p's avatar
    shafiz_p
    1 year ago

    Hi techsheep  

    You can easily achieve this in power bi by creating a static storage limit table, measure and matrix visual. Download PBIX file from the given link and check your desired result:
    How to format table in power bi 

     

    Steps:
    1. Load data to power bi and open power query and unpivot data. See image:

    Select first column, right click and select Unpivot other column. You will get the result below:

     

     

    2. Create a static table for storage limit using dax or power query or in excel then import in power bi. I have used Dax:
    Formula:

     

    Storage Limit = 
DATATABLE(
    "Game", STRING,
    "Storage Limit", INTEGER,
    {
        {"Game A", 150},
        {"Game B", 125},
        {"Game C", 50}
    }
)

     

     

     

    3. Create 3 measure:

     

    Total Storage = SUM('Table'[Value])


Storage Limit = 
    CALCULATE(
        SUM('Storage Limit'[Storage Limit]), 
        TREATAS(VALUES('Table'[Game]), 'Storage Limit'[Game])
    )


Storage Percentage = 
    DIVIDE([Total Storage], [Storage Limit])

     

     

    4. Select matrix visual. Place Game in column, and newly create measure in values and go to value format option and turn on switch values to rows. You are done. See image:

     

     

    Hope this helps!!

    If this solved your problem, please accept it as a solution and a kudos!!

     

     

    Best Regards,
    Shahariar Hafiz

rohit1991's avatar
rohit1991
Super User
1 year ago

Hi techsheep 

 

For solution could you please follow the following steps:

  1. Load data to Power BI and unpivot it in Power Query. See image:

Right-click on the first column > Unpivot Other Columns.

 

  1. Create a static table using DAX:
Storage Limit =
DATATABLE(
  "Game", STRING,
  "Storage Limit", INTEGER,
  {
       {"Game X", 300},
       {"Game Y", 250},
       {"Game Z", 180}
  }
)

Updated Data in Image:

 

  1. Create 3 Measures:
1.Total Storage = SUM('Table'[Value])
2.Storage Limit = 
  CALCULATE(
       SUM('Storage Limit'[Storage Limit]), 
       TREATAS(VALUES('Table'[Game]), 'Storage Limit'[Game])
  )
3.Storage Percentage = 
  DIVIDE([Total Storage], [Storage Limit])

 

  1. Add Matrix Visual and turn on “Switch values to rows”. See image: