Forum Discussion

jalaomar's avatar
jalaomar
Helper IV
4 years ago
Solved

Getting sum correct in a Matrix table

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 IDDivision Actual cost Forecast CostPOC%
ABeverage10005000 
ABeverage20001000 
ACommon10001000 
ABeverage40005000 
Total 80001200067%

 

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 IDDivision Actual cost Forecast CostPOC%
ABeverage10005000 
ABeverage20001000 
ACommon10001000 
ABeverage40005000 
Total 70001100064%

 

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

 

BR

J

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

5 Replies