Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
vishu263
Helper II
Helper II

Discrepancy in the total of PBI vs Excel export

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

Capture1.PNG

Total In Excel export against the same column is as follows

Capture2.PNG

 

Can somebody please guide whats going wrong here.

Thanks in advance.

1 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
        )
)

View solution in original post

10 REPLIES 10
tamerj1
Super User
Super User

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
        )
)

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.

Capture.PNG

 

@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
)
)

Really appreciate your help @tamerj1 .

Sorry to dissapoint you but it is still the same 😞

Capture.PNG

@vishu263 

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

@tamerj1 

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

@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
)
)

@tamerj1 ,

It is still the same.

Capture.PNG

@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. 

@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
        )
)

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.