topic Re: Error in Grand Total, Selected Value Returning Wrong Total when 'Select ALL' is Clicked in DAX Commands and Tips

Error in Grand Total, Selected Value Returning Wrong Total when 'Select ALL' is Clicked

Anonymous — Thu, 11 Apr 2024 06:24:31 GMT

Hi everyone, I need help in resolving multiple problems in my power BI visual.

The objective of the visual is to perform a comparative analysis for the actual cost of the Project with the original vendor and compare it to all the possible alternative vendors available on each of the state.

Actual Result:

Expected Result:

Here are the important features of my visual which I wanted to work well.

Feature 1: When a user selects alternate vendors using the 'MV. Vendor Name' slicer, it should update the value of 'Distribution X' and show the number of the vendors selected. This will be the basis of the computation where the Actual Hours and Cost will be equally distributed according to the number of alternative suppliers selected.

Problem: The measure for 'Distribution X' is working when the user would manually select on the 'MV. Vendor Name' slicer but once the user hits 'Select All' it will show all the possible suppliers ignoring the filters applied to the other slices (Project Name/State/Vendor)

DAX Code:

Distribution X = VAR TotalMV = CALCULATE(COUNTROWS('Project Matching'), ALL('Project Matching'[MV.Vendor Name])) VAR SelectedMV = COUNTROWS(ALLSELECTED('Project Matching'[MV.Vendor Name])) RETURN IF(TotalMV = SelectedMV, TotalMV, SelectedMV)

Feature 2: After determining the cost and hour allocations, the Cost using the New Rates (NR Cost) is now computed via measure. This will be the basis later on for the row per row computation of the savings.

Problem: The measure 'NR Cost' is calculating correctly on a row per row basis, however, I expect the grandtotal to sum all the rows, however my beginner knowledge is limited. I tried applying the ones in the forum but I think I haven't really understood how Sumx iterator could do really.

Dax Code:

NR Cost = VAR mv_rate = [MV Rate] * [Hrs_Alloc] RETURN IF( ISBLANK(mv_rate) || mv_rate = 0 , [Cost W Inflation]/[Distribution X], mv_rate)

Feature 3: Line per line computation of the Savings/Overage using the measure 'Savings/Over'

Problem: As you can see in the visual my code is not returning any value at all 😭

Dax Codes:

Cost_Alloc = VAR SelectedMV = COUNTROWS(ALLSELECTED('Project Matching'[MV.Vendor Name])) VAR CurrentMV = SELECTEDVALUE('Project Matching'[MV.Vendor Name], "Default") VAR acounta = [Cost W Inflation] RETURN IF( HASONEVALUE('Project Matching'[MV.Vendor Name]), IF(SelectedMV <> 0, acounta / SelectedMV, BLANK()), SUMX(VALUES('Project Matching'[MV.Vendor Name]), [Cost W Inflation]) / SelectedMV ) Savings/Over = [NR Cost]-[Cost_Alloc]

I attached my dummy data and pbix in the link, should you need more context about the matter.

Power BI file and Excel File

Thanks so much for the help!

Re: Error in Grand Total, Selected Value Returning Wrong Total when 'Select ALL' is Clicked

Anonymous — Fri, 12 Apr 2024 07:20:36 GMT

Hi Anonymous ,

Based on the information you have provided, the issue with metrics not being updated correctly appears to be related to how the function is interpreted in the slicer context. Please consider modifying all filters in the metrics clearing table except for the filters to be retained:

Distribution X = VAR TotalMV = CALCULATE ( COUNTROWS ( 'Project Matching' ), ALL ( 'Project Matching'[MV.Vendor Name] ) ) VAR SelectedMV = COUNTROWS ( ALLEXCEPT ( 'Project Matching', 'Project Matching'[Project Name], 'Project Matching'[State], 'Project Matching'[Vendor] ) ) RETURN IF ( TotalMV = SelectedMV, TotalMV, SelectedMV )

The issue of totals not being aggregated correctly is a common problem in Power BI, especially when working with complex calculations that need to be aggregated in different ways at the total level. You can use this function on a table that follows the current filter context and then perform row-level calculations in that context:

NR Cost = SUMX ( VALUES ( 'Project Matching'[Project Name] ), VAR mv_rate = [MV Rate] * [Hrs_Alloc] RETURN IF ( ISBLANK ( mv_rate ) || mv_rate = 0, [Cost W Inflation] / [Distribution X], mv_rate ) )

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Error in Grand Total, Selected Value Returning Wrong Total when 'Select ALL' is Clicked

Anonymous — Sun, 14 Apr 2024 23:43:08 GMT

Good morning @v-yifanw-msft

Unfortunately the measures you suggested did not work (please see image with Distribution X2 and NR Cost 2 as the new measures.)

My goal is take out the tendency of the measure returning the wrong number once 'select all' is clicked by the user in the MV. Vendor Name Slicer.

For the NR Cost, it seems that we happen to almost the same logic, but the grand total is still not correct.

For more context I upload the PBIX file and the ref data in this link: https://drive.google.com/drive/u/1/folders/1jrzCzmXR7zcFNk5gR4KM5L9VGGHLufbq

I appreciate your help! Thank you so much😭