Total End Formula

[3mmanuel_12]

Hello everyone,

I am new to Power BI and currently working with sample sales data. I’ve encountered a calculation challenge and was hoping someone could help.

I have a summary of sales, and I would like to calculate the total sales based on a specific set of rows — for example, rows with indices 3, 4, 5, 6, 7, and 13.

Has anyone worked on a similar scenario or could advise on the best approach to achieve this?

Thank you in advance for your guidance!

[zenisekd · Accepted solution]

Aw, I see, you dont want to sum all the rows belonging to the segment, rather you sum only certain rows in the segment. In that case forget about my solution.

[Jaywant-Thorat · Accepted solution]

Hi @3mmanuel_12 
You can try this. It will convert that text into a virtual table inside a measure, with no extra tables.

My Total :=
VAR IsFormula = SELECTEDVALUE('Cernol Template'[Totalling Type]) = "Formula"
VAR FormulaText = SELECTEDVALUE('Cernol Template'[Totalling]) -- e.g. "3,4,5,6,7,13"

VAR FormulaTable =
ADDCOLUMNS(
TEXTSPLIT(FormulaText, ","), -- → {"3","4","5","6","7","13"}
"Seq", VALUE([Value]) -- convert to number
)

RETURN
IF(
IsFormula,
CALCULATE(
'_Acc Ledger'[Total Amount],
TREATAS(FormulaTable, '_Acc schedule'[L1 Sequence])
),
'_Acc Ledger'[Total Amount]
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Jaywant Thorat | MCT | Data Analytics Coach
Linkedin: https://www.linkedin.com/in/jaywantthorat/

[v-dineshya · Accepted solution]

Hi @3mmanuel_12 ,

Thank you for reaching out to the Microsoft Community Forum.

Please try below two solutions.

1. Refer below DAX measure.

Total Sales Selected =
CALCULATE(
SUM('Sales'[Amount]),
'Sales'[Header] IN {
"FOOD INDUSTRY",
"GENERAL INDUSTRIAL",
"HORECA",
"QSR",
"HEALTHCARE",
"HYGIENE SHOP"
}
)

2.   You can add a logical/grouping column in Power Query.

Add Column --> Conditional Column

Example:

Header                     IncludeInTotal
FOOD INDUSTRY           1
HORECA                        1
GENERAL INDUSTRIAL  1

Total Sales Selected =
CALCULATE(
SUM('Sales'[Amount]),
'Sales'[IncludeInTotal] = 1
)

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[v-dineshya]

Hi @3mmanuel_12 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @3mmanuel_12 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[Jaywant-Thorat]

Understanding the Problem: You want to calculate Total Sales based on a specific set of rows, e.g., rows 3, 4, 5, 6, 7, 13, similar to how your Excel sheet uses a formula row.

Solution: First understand that, Row numbers doesnt exist in Power BI, so we need to create an INDEX helper-column in your table, which will act as row identifier and then create a measure that sums only selected rows.

Step1) To add Row Index Number, follow the path:

Home -> Transform Data -> Power Query Editor -> Add Column -> Index Colum
Step2) Create a measure that sums only selected rows

Total Sales (Selected Rows) = CALCULATE( SUM('Sales'[Amount]), 'Sales'[Index] IN {3,4,5,6,7,13} )

Done. 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Jaywant Thorat | MCT | Data Analytics Coach
Linkedin: https://www.linkedin.com/in/jaywantthorat/

[zenisekd]

Hi @3mmanuel_12 
You could achieve this by adding a groupped column, which would distinct the total sales category supposed to be. Either directly in Power BI desktop or in Power query, then you create a matrix, where you use this new column with Header in a matrix and amount in values 🙂 

In the visual format you can later play with what totals you want to see.

[3mmanuel_12]

You can tell me if i'm wrong but in my opinion , i cannot use this method bcs all formulas in the column Totalling will be dynamic.

[zenisekd · Accepted solution]

Aw, I see, you dont want to sum all the rows belonging to the segment, rather you sum only certain rows in the segment. In that case forget about my solution.

[amitchandak]

@3mmanuel_12 , We need to add these to the table as an additional row , you can add them in separate table and append in power query or DAX. You need have order for that. 

And then you can have calculation like 

if(Max(Table[Header]) = "Sales Total 1", calculate([Measure], Filter(all(Header), Table[Header Order] in {3,4,5,6,713} ) ), [Measure]) 

You can use switch and for both Sales Total 1 and Sales Total 2

I have done using additional table, but can be done using same table too

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

[3mmanuel_12]

I have already use the same way : 

VAR calcul_=SELECTEDVALUE('Cernol Template'[Totalling Type])

VAR value_=

    SWITCH(

        TRUE(),

        calcul_="Formula",

        CALCULATE(

                '_Acc Ledger'[Total Amount],

                '_Acc schedule'[L1 Sequence] IN {3,4,5,6,713} 

            ),

        '_Acc Ledger'[Total Amount],

    )

But here is the issue , in the Totalling column i have text format. I don't know how to convert this part  3,4,5,6,713 to a table without creating a new table to store all formula.

[Jaywant-Thorat · Accepted solution]

Hi @3mmanuel_12 
You can try this. It will convert that text into a virtual table inside a measure, with no extra tables.

My Total :=
VAR IsFormula = SELECTEDVALUE('Cernol Template'[Totalling Type]) = "Formula"
VAR FormulaText = SELECTEDVALUE('Cernol Template'[Totalling]) -- e.g. "3,4,5,6,7,13"

VAR FormulaTable =
ADDCOLUMNS(
TEXTSPLIT(FormulaText, ","), -- → {"3","4","5","6","7","13"}
"Seq", VALUE([Value]) -- convert to number
)

RETURN
IF(
IsFormula,
CALCULATE(
'_Acc Ledger'[Total Amount],
TREATAS(FormulaTable, '_Acc schedule'[L1 Sequence])
),
'_Acc Ledger'[Total Amount]
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Jaywant Thorat | MCT | Data Analytics Coach
Linkedin: https://www.linkedin.com/in/jaywantthorat/