Forum Discussion
Total End Formula
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!
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.
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/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 1Total 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
10 Replies
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 rowsTotal 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/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 likeif(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 2I 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-e39ca822908aI 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.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/
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.
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.
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.
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 1Total 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
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
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
