Creating Custom Table Row

[dalixwill]

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]))
    )
)

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?

[amitchandak · Accepted solution]

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