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
jlayugan
Regular 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! 

2 REPLIES 2
v-yifanw-msft
Community Support
Community Support

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
Fabcon_Europe_Social_Bogo

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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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