Reply
dalixwill
Regular Visitor
Partially syndicated - Outbound

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 TypeCartons SoldTarget Cartons to SellPenetration
Blackberries6100.6
Blueberries6150.4
Cranberries350.6
Strawberries10101.0
TOTAL25400.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]))
    )
)

 

 

 
 
For the most part, this work-around returns the desired result.

Here are the open issues
  1. 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.
  2. 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?

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

Syndicated - Outbound

@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...

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

Syndicated - Outbound

@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...

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)