- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Creating Custom Table Row
Consider the following scenario...
Amy's Berry Farm has a plethora of berries on offer at the farmers market, including, but not limited to, blueberries, blackberries, cranberries and strawberries.
The data in fields Berry Type, Cartons Sold and Target Cartons to Sell are sourced from an import, whereas the field Penetration is a measure computed as the quotient of Cartons Sold and Target Cartons to Sell.
Given a dashboard users selection of berry types, we want the row TOTAL to display the following values according to field:
1. TOTAL Cartons Sold == SUM(Cartons Sold)
2. TOTAL Target Cartons to Sell == SUM(Target Cartons to Sell)
3. TOTAL Penetration == SUM(Cartons Sold) / SUM(Target Cartons to Sell)
as shown below for the hypothetical selection of four berries from a much larger list.
Berry Type | Cartons Sold | Target Cartons to Sell | Penetration |
Blackberries | 6 | 10 | 0.6 |
Blueberries | 6 | 15 | 0.4 |
Cranberries | 3 | 5 | 0.6 |
Strawberries | 10 | 10 | 1.0 |
TOTAL | 25 | 40 | 0.625 |
Unfortunately, the TOTAL value for field Penetration is showing a value other than 0.625 when the native Values format option is selected in the table visualization.
As a work-around, the following syntax was used to create a new table Combined Berry Sales Table
Combined Berry Sales Table =
UNION(
SELECTCOLUMNS(
berry_sales,
"Berry Type", berry_sales[berryType],
"Cartons Sold", berry_sales[cartonsSold],
"Target Cartons to Sale", berry_sales[salesTarget],
"Penetration", [penetration]
),
ROW(
"Berry Type", {"TOTAL"},
"Cartons Sold", SUM(berry_sales[cartonsSold]),
"Target Cartons to Sale", SUM(berry_sales[salesTarget]),
"Penetration", DIVIDE(SUM(berry_sales[cartonsSold]), SUM(berry_sales[salesTarget]))
)
)
Here are the open issues
- The table Combined Berry Sales Table is not affected by berry selections
Irrespective of the users selection of berry types, the entire list (more than 4 shown berry types) is present. Attempts to create a relationship results in loss of the TOTALS row, which is not a berry. - The row TOTAL does not appear last
Berries such as Wineberry and Wolfberry appear in rows after TOTAL, as the default sort order is alphabetical.
My question is
How can one create a custom TOTALS row for a table where field values are measures based on selections and the TOTALS row always appears last?
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@dalixwill , We need to add an additional column to a dimension distinct Berry Type.
ANd then for each value that does have value in the table, we have write a code to get the value.
I have set up an example in case of P&L, I used one additional dim, which you can avoid in this case
Power BI How to get the P&L formatting right: https://youtu.be/C9K8uVfthUU
Power BI How to get two columns format Profit and Loss Statement(P&L) right: https://youtu.be/WLg85yiMgHI
https://medium.com/microsoft-power-bi/power-bi-formatted-p-l-with-custom-sub-totals-and-blank-rows-e...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@dalixwill , We need to add an additional column to a dimension distinct Berry Type.
ANd then for each value that does have value in the table, we have write a code to get the value.
I have set up an example in case of P&L, I used one additional dim, which you can avoid in this case
Power BI How to get the P&L formatting right: https://youtu.be/C9K8uVfthUU
Power BI How to get two columns format Profit and Loss Statement(P&L) right: https://youtu.be/WLg85yiMgHI
https://medium.com/microsoft-power-bi/power-bi-formatted-p-l-with-custom-sub-totals-and-blank-rows-e...

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
07-31-2024 03:09 AM | |||
07-17-2023 01:16 PM | |||
Anonymous
| 02-15-2023 05:02 AM | ||
03-25-2024 08:24 PM | |||
Anonymous
| 04-14-2018 10:32 AM |
User | Count |
---|---|
86 | |
82 | |
42 | |
40 | |
35 |