Average of categories over vendor
Hello, I'm trying to calculate the category average of vendors, but view the specific vendor. I need to make my formula calculate the score of the category that the selected vendor is in. It currently is showing the same average value for all 4 categories, when they should be different. What do I need to change in my formula? %_VendorScore_Avg_Category = VAR brand = 0.14 VAR eob = 0.13 VAR finance = 0.17 VAR service = 0.18 VAR supplychain = 0.14 VAR technology = 0.09 VAR opportunity = 0.15 VAR SelectedCategory = SELECTEDVALUE ( 'Vendor Scorecard'[Category] ) RETURN CALCULATE ( ([#_BrandScore_Avg] * brand) + ([#_EaseofBusiness_Avg] * eob) + ([#_Finance_Avg] * finance) + ([#_Service_Avg] * service) + ([#_SupplyChain_Avg] * supplychain) + ([#_Technology_Avg] * technology) + ([#_Opportunity_Avg] * opportunity), REMOVEFILTERS ('Vendor Scorecard'[Vendor Name]), 'Vendor Scorecard'[Category] = SelectedCategory )
Disable Interaction between chart and Slicer via DAX
Dear All, I have created a report with several tables and a chart using fields as parameters so I can switch easily between the 4 different values. There is also a date slicer on the page. However, for one of the values in the chart I want every filter to remain except the date slicer which I want to show different date values (the tables show historic dates as per the date slicer but, for this one specific chart value, I want to show different date values - future dates). I can achieve this by unlinking the chart interactions but this then means it’s incorrect for the other field values which I still want to respond to the date slicer. I therefore need to do this via DAX. I’m not sure if it’s relevant to the issue but the measure is based on a secondary date relationship ([FINAL DELAY] to [Date]) and the data is filtered to only include specific rows (in this case when dates in V_TIMESTAMP are in Oct 23 - the intention is that this will eventually become a dynamic value based on a slicer selection but that’s a problem for another day). I’m sure this would be achievable using some combination of All, Allexcept, keepfilters, etc, but so far I cannot get this to work. Any ideas anyone? The current base formula is as follows: Backlog (Chart) = CALCULATE(sum(Backlog[NET_VALUE_BALANCE_CHF]), FILTER(ALL(Backlog[V_TIMESTAMP]), Backlog[V_TIMESTAMP] >= DATE(2023, 10, 1) && Backlog[V_TIMESTAMP] <= DATE(2023, 10, 31)), USERELATIONSHIP(Backlog[FINAL_DELAY], 'Calendar'[Date]) ) As mentioned, this works if the chart/date slicer interaction is removed. The intention is that the above measure still works with the interaction still enabled. Thanks in advance
Remove Table Filters While Using Virtual Table Measures
Hello! I'm currently trying to return the max value from a virtual table that I will use in another virtual table, along with removing the table filters when I place this max value in a table visual. Specifically, the 'MAX Efficiency Score' measure will be utilized in the 'Efficiency Score' measure (see 'var table7' in the 'Efficiency Score' measure). Efficiency Score = var table1 = SUMMARIZE('Fuel (Traveling)', 'Fuel (Traveling)'[Driver Fleet Name], 'Fuel (Traveling)'[DriverName/ID], "EffScore", -[True Avg. Travel Difference]) var table2 = ADDCOLUMNS(table1, "DriverCountFleet", CALCULATE(DISTINCTCOUNT('Fuel (Traveling)'[DriverID]), ALLEXCEPT('Fuel (Traveling)', 'Fuel (Traveling)'[Driver Fleet Name], 'Combined Calendar'[Date - Copy])), "OrderCountFleet", CALCULATE(DISTINCTCOUNT('Fuel (Traveling)'[Order ID]), ALLEXCEPT('Fuel (Traveling)', 'Fuel (Traveling)'[Driver Fleet Name], 'Combined Calendar'[Date - Copy])), "OrderCountDriver", CALCULATE(DISTINCTCOUNT('Fuel (Traveling)'[Order ID])) + 0) var table4 = ADDCOLUMNS(table2, "AvgOrders", DIVIDE([OrderCountFleet], [DriverCountFleet])) var table5 = ADDCOLUMNS(table4, "Ratio", DIVIDE([OrderCountDriver], [AvgOrders])) var table6 = ADDCOLUMNS(table5, "Score", IF([Ratio] > 1, [EffScore] * 1, [EffScore] * [Ratio])) var table7 = ADDCOLUMNS(table6, "FinalScore", [Score]/[MAX Efficiency Score]) var table8 = SUMX(table7, [Score]) return table8 MAX Efficiency Score = var table1 = SUMMARIZE('Fuel (Traveling)', 'Fuel (Traveling)'[Driver Fleet Name], 'Fuel (Traveling)'[DriverName/ID], "EffScore", -[True Avg. Travel Difference]) var table2 = ADDCOLUMNS(table1, "DriverCountFleet", CALCULATE(DISTINCTCOUNT('Fuel (Traveling)'[DriverID]), ALLEXCEPT('Fuel (Traveling)', 'Fuel (Traveling)'[Driver Fleet Name], 'Combined Calendar'[Date - Copy])), "OrderCountFleet", CALCULATE(DISTINCTCOUNT('Fuel (Traveling)'[Order ID]), ALLEXCEPT('Fuel (Traveling)', 'Fuel (Traveling)'[Driver Fleet Name], 'Combined Calendar'[Date - Copy])), "OrderCountDriver", CALCULATE(DISTINCTCOUNT('Fuel (Traveling)'[Order ID])) + 0) var table4 = ADDCOLUMNS(table2, "AvgOrders", DIVIDE([OrderCountFleet], [DriverCountFleet])) var table5 = ADDCOLUMNS(table4, "Ratio", DIVIDE([OrderCountDriver], [AvgOrders])) var table6 = ADDCOLUMNS(table5, "Score", IF([Ratio] > 1, [EffScore] * 1, [EffScore] * [Ratio])) var final = ADDCOLUMNS(table6, "MaxValue", MAXX(table6, [Score])) return CALCULATE(MAXX(final, [MaxValue]), ALL('Fuel (Traveling)')) My current issue is that I can't get the 'MAX Efficiency Score' measure to return the max score of 79.58 (in the case below) for each row of my table visual below. For background, the table visual below contains the same fields (Driver Fleet Name and DriverName/ID) that are used in the SUMMARIZE functions above. I also set the dashboard up so the 'Efficiency Score' and 'MAX Efficiency Score' values dynamically update when the date slider is used, which is why I referenced the 'Combined Calendar[Date - Copy] field in the measures above. I'd really appreciate any help I could get on this issue. Thanks!
