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 is static numbers.

Row 4 is percentage which is simply calculated by Storage / Storage Limit (e.g., = D2/D3)

 

Data Source:

 

My goal is to format the source data like the table above, which will update with new "Over/Under Limit %" and total "Storage" number for each game when users select "PC" vs "Mobile" Type. The "Storage Limit" is static and never changes. 

 

Is there a way to set this up in Power BI Desktop or Services?

 

File: https://we.tl/t-89xQIa4SKg

 

 

 

 

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

2 Replies

  • shafiz_p's avatar
    shafiz_p
    Super User
    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: