Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
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
)
)
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.
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
)
)
Project ID and Project Status placed in tge table visual are from which table(s)?
Columns 'Project ID' and 'Project Status' are coming from 'Project Attributes' table.
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
)
)
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.
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
)
)
User | Count |
---|---|
51 | |
38 | |
20 | |
14 | |
13 |
User | Count |
---|---|
98 | |
71 | |
29 | |
20 | |
13 |