Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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.
Thanks so much for the help!
Solved! Go to Solution.
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.
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
User | Count |
---|---|
21 | |
19 | |
12 | |
9 | |
7 |
User | Count |
---|---|
30 | |
27 | |
14 | |
13 | |
11 |