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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
jlayugan
Frequent Visitor

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

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:

jlayugan_1-1712815088736.png

Expected Result: 

jlayugan_2-1712815839928.png

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! 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @jlayugan ,

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.

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Hi @jlayugan ,

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.

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😭
 
 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.