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: 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!
DAX - KEEPFILTERS not showing correct results - arbitrarily shaped set
I am doing some training work in AdventureWorksDW database. I have two measures: Sum of SalesAmount = SUM(FactInternetSales[SalesAmount]) MonthlyAverageSales = AVERAGEX ( VALUES(DimDate[EnglishMonthName]), [Sum of SalesAmount] ) I have developed the above report, choosing only two years 2006 and 2007, and only 4 months using slicers. While the measure [Sum of SalesAmount] shows the correct value, the other measure [MonthlyAverageSales] shows incorrect data in the Total row; however, both measures are right, when aggregated for each year 2006 and 2007. The measure [MonthlyAverageSales] at the Total row should actually be $ 5,916,696.73/8, which is $ 739,587.09. (8 in denominator represents 8 months - Jan,Feb,Nov,Dec for each year - 2006 and 2007) Instead, what I see is $ 5,916,696.73/4, which is $1,479,174.18; (4 in denominator represents 4 months- Jan,Feb,Nov,Dec, combining both years together) Now, I have changed the formula, and have created a new measure: MonthlyAverageCorrect = AVERAGEX ( KEEPFILTERS(VALUES(DimDate[EnglishMonthName])), [Sum of SalesAmount] ) I still do not see any change. Instead of getting $ 739,587.09, I still see $1,479,174.18. Where am I in error ?
