Discrepancy in the total of PBI vs Excel export

[vishu263]

Hello,

I have written a below DAX measure to calculate 'assigned' value & it is giving me the proper output when I see it row by row. However, the total is not showing up correctly.
When I export the same table in excel, I get the correct total i.e. 1708912.88

Total Assigned 1.1 = IF(SELECTEDVALUE('Project Attributes'[Project Status])<>"REL",SUM('Cost Summary Fact'[YTD Actual]),
IF(SUM('Cost Summary Fact'[Revised Budget FY])>=SUM('Cost Summary Fact'[YTD Actual]),SUM('Cost Summary Fact'[Revised Budget FY]),
IF(SUM('Cost Summary Fact'[Revised Budget FY])<SUM('Cost Summary Fact'[YTD Actual]),SUM('Cost Summary Fact'[YTD Actual]),
IF(ISBLANK(SUM('Cost Summary Fact'[Revised Budget FY])),SUM('Cost Summary Fact'[YTD Actual]),
IF(ISBLANK(SUM('Cost Summary Fact'[YTD Actual])),SUM('Cost Summary Fact'[Revised Budget FY]))))))

Total In PBI Report is as follows

Total In Excel export against the same column is as follows

Can somebody please guide whats going wrong here.

Thanks in advance.

[tamerj1]

Hi @vishu263 
Please try

Total Assigned 1.1 =
SUMX (
    FILTER (
        VALUES ( 'Project Attributes'[Project Status] ),
        'Project Attributes'[Project Status] <> "REL"
    ),
    VAR RevisedBudgetFY =
        CALCULATE ( SUM ( 'Cost Summary Fact'[Revised Budget FY] ) )
    VAR YTDActual =
        CALCULATE ( SUM ( 'Cost Summary Fact'[YTD Actual] ) )
    RETURN
        SWITCH (
            TRUE (),
            RevisedBudgetFY >= YTDActual, RevisedBudgetFY,
            RevisedBudgetFY < YTDActual, YTDActual,
            ISBLANK ( RevisedBudgetFY ), YTDActual,
            ISBLANK ( YTDActual ), RevisedBudgetFY
        )
)

[vishu263]

Thanks @tamerj1 for your response. I used the DAX mentioned by you and named it Total Assigned 1.3

This total is not coming as expected both in PBI and Excel export. The expected total is 1708912.88

Please find the below snapshot.

[tamerj1]

@vishu263 

Please try

Total Assigned 1.3 =
SUMX (
FILTER (
SUMMARIZE (
'Project Attributes',
'Project Attributes'[Project Status],
'Project Attributes'[Project ID]
),
'Project Attributes'[Project Status] <> "REL"
),
VAR RevisedBudgetFY =
CALCULATE ( SUM ( 'Cost Summary Fact'[Revised Budget FY] ) )
VAR YTDActual =
CALCULATE ( SUM ( 'Cost Summary Fact'[YTD Actual] ) )
RETURN
SWITCH (
TRUE (),
RevisedBudgetFY >= YTDActual, RevisedBudgetFY,
RevisedBudgetFY < YTDActual, YTDActual,
ISBLANK ( RevisedBudgetFY ), YTDActual,
ISBLANK ( YTDActual ), RevisedBudgetFY
)
)

[vishu263]

Really appreciate your help @tamerj1 .

Sorry to dissapoint you but it is still the same 😞

[tamerj1]

@vishu263 

Project ID and Project Status placed in tge table visual are from which table(s)?

[vishu263]

@tamerj1 

Columns 'Project ID' and 'Project Status' are coming from 'Project Attributes' table.

[tamerj1]

@vishu263 

Total Assigned 1.3 =
SUMX (
FILTER (
SUMMARIZE (
'Cost Summary Fact',
'Project Attributes'[Project Status],
'Project Attributes'[Project ID]
),
'Project Attributes'[Project Status] <> "REL"
),
VAR RevisedBudgetFY =
CALCULATE ( SUM ( 'Cost Summary Fact'[Revised Budget FY] ) )
VAR YTDActual =
CALCULATE ( SUM ( 'Cost Summary Fact'[YTD Actual] ) )
RETURN
SWITCH (
TRUE (),
RevisedBudgetFY >= YTDActual, RevisedBudgetFY,
RevisedBudgetFY < YTDActual, YTDActual,
ISBLANK ( RevisedBudgetFY ), YTDActual,
ISBLANK ( YTDActual ), RevisedBudgetFY
)
)

[vishu263]

@tamerj1 ,

It is still the same.

[tamerj1]

@vishu263 

The 1,708,912.88 that you are getting in excel is for sure a wrong number. The 439,192.12 total that you are getting in the power bi table visual looks reasonable. Please double check your excel calculations. 

[tamerj1 · Accepted solution]

@vishu263 

Please try

Total Assigned 1.3 =
SUMX (
    SUMMARIZE (
        'Cost Summary Fact',
        'Project Attributes'[Project Status],
        'Project Attributes'[Project ID]
    ),
    VAR RevisedBudgetFY =
        CALCULATE ( SUM ( 'Cost Summary Fact'[Revised Budget FY] ) )
    VAR YTDActual =
        CALCULATE ( SUM ( 'Cost Summary Fact'[YTD Actual] ) )
    RETURN
        SWITCH (
            TRUE (),
            'Project Attributes'[Project Status] <> "REL", YTDActual,
            RevisedBudgetFY >= YTDActual, RevisedBudgetFY,
            RevisedBudgetFY < YTDActual, YTDActual,
            ISBLANK ( RevisedBudgetFY ), YTDActual,
            ISBLANK ( YTDActual ), RevisedBudgetFY
        )
)