Getting sum correct in a Matrix table

[jalaomar]

Hello, 

I am facing an issue with a report where I visualize financial data in a matrix table, just to provide a simple example I have 3 measure 

1. 

Total Actual cost = CALCULATE(

SUM('Data Rel'[Actual Cost]), REMOVEFILTERS('Data Rel'[Division]))

2. 

Total Forecast cost = CALCULATE( SUM('Data Rel'[Forecast Cost]), REMOVEFILTERS('Data Rel'[Division]))

3. 

POC % = CALCULATE( DIVIDE('Data Rel'[Total Actual cost],'Data Rel'[Total Forecast cost]), REMOVEFILTERS('Data Rel'[Division]))

Note that in my data, there is column regarding the Division for a Projects 

The following table is the expected outcome:

Project ID	Division	Actual cost	Forecast Cost	POC%
A	Beverage	1000	5000
A	Beverage	2000	1000
A	Common	1000	1000
A	Beverage	4000	5000
Total		8000	12000	67%

But in my Matrix table, when I add the Category I get the following results, which is incorrect. since sum of Actual cost 7000 and is excluding the row where the Category is equal to "Common".

Project ID	Division	Actual cost	Forecast Cost	POC%
A	Beverage	1000	5000
A	Beverage	2000	1000
A	Common	1000	1000
A	Beverage	4000	5000
Total		7000	11000	64%

Anyone know how to manage this issue as I have no control of the data input.

BR

J

[amitchandak]

@jalaomar , Not very clear. if you need grand total then try like

1.
Total Actual cost = CALCULATE(
SUM('Data Rel'[Actual Cost]), allselected())

2.
Total Forecast cost = CALCULATE( SUM('Data Rel'[Forecast Cost]), allselected())

[jalaomar]

Hi,

Just tried it and it didn't provide the Grand total 😞

[Greg_Deckler]

@jalaomar This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[jalaomar]

Hi,

tried out the suggested measures but that didn't work eaither 😞

Below is an example of what happens when I add the Division column in the Matrix Table 

The totals looks OK, but I only want to show one row of the Division (Beverage), together with total values 

So then I filter on the Net Sales price greater than 0

It Provides me the summary of the Division but then lose some of totals for Actual Cost, as you can see the second row level with some actual cost dissapears.

any clue on how I can manage this correctly?

[Anonymous · Accepted solution]

Hi @jalaomar ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. 

1. Update the formula of measure [Total Actual cost] and [Total Forecast cost]

Total Actual cost = 
IF (
    SELECTEDVALUE ( 'Data Rel'[Division] ) = "Beverage",
    CALCULATE (
        SUM ( 'Data Rel'[Actual Cost] ),
        ALLSELECTED ( 'Data Rel'[Division] )
    ),
    BLANK ()
)

Total Forecast cost = 
IF (
    SELECTEDVALUE ( 'Data Rel'[Division] ) = "Beverage",
    CALCULATE (
        SUM ( 'Data Rel'[Forecast Cost] ),
        ALLSELECTED ( 'Data Rel'[Division] )
    ),
    BLANK ()
)

2. Create two measures as below to get the correct total sum of actual cost and forecast cost

Actual = 
IF (
    ISINSCOPE ( 'Data Rel'[Division] ),
    [Total Actual cost],
    SUMX (
        GROUPBY ( 'Data Rel', 'Data Rel'[Project ID], 'Data Rel'[Division] ),
        [Total Actual cost]
    )
)

Forecast = 
IF (
    ISINSCOPE ( 'Data Rel'[Division] ),
    [Total Forecast cost],
    SUMX (
        GROUPBY ( 'Data Rel', 'Data Rel'[Project ID], 'Data Rel'[Division] ),
        [Total Forecast cost]
    )
)

3. Update the formula of measure [POC %] as below

POC % = DIVIDE([Actual],[Forecast])

In addition, you can refer the method in the following links to handle with incorrect total values on the matrix visual...

Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand

Dax for Power BI: Fixing Incorrect Measure Totals

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards