Forum Discussion
How the grouping causes the nagative value?
I have a parameter called Current Year(RM), it stores decimal values. for Category I created two new groups so it become Category(group) and Category(group)(group).
in Category i have value Tax Expense.
then in Category(group) i group this Tax Expense and rename it to Tax_Expense.
then in Category(group)(group) i group and rename to 3.0 Taxes.
i created table matrix.
matrix 1: i pull category(group) and Current Year(RM)
matrix 2: i pull category(group)(group) and Current Year(RM)
why under matrix 1 positive value; 48; but matrix 2 negative value; (48)?
the reason this frustrates me is because i need total to be -1404 which is the summation of -48+(-1356).
i need to uderstand the grouping, why does it causing the value to be -ve. the items under 3.0 Tax Expense is just Tax Expense.
i dont mind if the value shows Tax Expense=48 and Operating Surplus/Deficit=-1356 but would i be able to manually define the total row to be Operating Surplus/Deficit - Tax Expense? as i know grand total is just summation of everything...
Thank you for your support this case MFelix
Hi, nadia_ab
Thank you very much for asking about the matrix total. As you described, you want to change the calculation logic in the grand total to Operating Surplus/Deficit - Tax Expense. It's possible. At present, the main method is to use the isinscope or hasvalues functions in an IF judgment to determine whether the current row is a total row.
Here's a simple example:My measure:
Is detail row = ISINSCOPE('Table'[Category(Group)])Origin cal Measure = SUM('Table'[Values])Fix grand total cal = IF(ISINSCOPE('Table'[Category(Group)]),[Origin cal Measure],[Origin cal Measure]/100)Here's what they look like in the matrix:
This makes it possible to perform a separate calculation logic for the grand total row of the matrix. You can use this function and then adjust it to display the values correctly based on your actual calculations.
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Thank you for your support this case MFelix
Hi, nadia_ab
Thank you very much for asking about the matrix total. As you described, you want to change the calculation logic in the grand total to Operating Surplus/Deficit - Tax Expense. It's possible. At present, the main method is to use the isinscope or hasvalues functions in an IF judgment to determine whether the current row is a total row.
Here's a simple example:My measure:
Is detail row = ISINSCOPE('Table'[Category(Group)])Origin cal Measure = SUM('Table'[Values])Fix grand total cal = IF(ISINSCOPE('Table'[Category(Group)]),[Origin cal Measure],[Origin cal Measure]/100)Here's what they look like in the matrix:
This makes it possible to perform a separate calculation logic for the grand total row of the matrix. You can use this function and then adjust it to display the values correctly based on your actual calculations.
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous, thank you for giving me ideas.
Meaning, if I want to use this approach, and i also have 3 other columns; PreviousYear, Difference and %Difference, i need to create 3 sets of new measure that corresponds for each column, right?
Hi, nadia_ab
Thank you very much for your reply. If your other measure totals are incorrect, then you need to correct your measure using the method above.
Let's take a look at SQLBI's articles to enhance your understanding of current problems and functions, which will help you find the right solution in the future when you encounter Total incorrect.
Why Power BI totals might seem wrong - SQLBI
Distinguishing HASONEVALUE from ISINSCOPE - SQLBI
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- MFelix
Super User
Hi nadia_ab ,
When you refer that you make the groups and renaming, what is the calculation you are doing in each one? Is there any particular value?
Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.
If the information is sensitive please share it trough private message. Hi MFelix, thanks for replying.
This workbook that i have is actually from different developer which is no longer in presence.
As far as i checked, I can only assume (based on the generic naming Category(group) and Category(group)(group) ) she performs the grouping by right-click on the table and create New Group. So by this method, there supposed to be no additional calculation performed, (again I assume).
I am sharing the workbook with you here
Ticket_no2.pbix
