Forum Discussion
Weighted Average Slicer Selection
Hey everyone, I am running into issues trying to create a DAX formula using a parameter table that changes the weighted average calculation with the click of a slicer option.
I want to give the option to the users of selecting different weights of six scores. For example:
Slicer Option 1 = Score 1 accounts for 50% of the Weighted Average (WA).
Score 2 through 6 account for 10% of the WA calculation.
Slicer Option 2 = Score 1 .... 25% WA
Score 2 .... 15%
Score 3 .... 5%
Score 4 .... 10%
Score 5 .... 30%
Score 6 .... 5%
Slicer Option 3..... you get the idea.
What would be really cool is if the users can enter their own weights as long as it adds up to 100% but as far as I know, that can only be done using regular excel.
Is there a way to achieve this? I was able to do some research and use a blog post from SqlJason to try to use the parameter table approach but I am not sure how to define the weighted averages for this approach.
http://sqljason.com/2012/11/measure-selection-using-slicers-in.html
Any sugggestions on how I should go about this would be much appreciated, thanks in advance.
3 Replies
Since you just have 5-6 options, you can make measures for each option. Once you have made those 5-6 measures, you can use the technique I described in my blog straightaway.
Although if it was excel, I think I would have done it differently as I can actually enter the values that I want for the weights
I have made 5 measures for each option in my FACT table but it returns an error in the msr_value calculation:
"the sum function only accepts a column reference as the argument number 1"
MSR_VALUE: = SWITCH( TRUE,
MIN(MSR[MSR_ID])=1,
SUM(FACT[MEASURE_1]),
MIN(MSR[MSR_ID])=2,
SUM(FACT[MEASURE_2]),
MIN(MSR[MSR_ID])=3,
SUM(FACT[MEASURE_3]),
MIN(MSR[MSR_ID])=4,
SUM(FACT[MEASURE_4]),
MIN(MSR[MSR_ID])=5,
SUM(FACT[MEASURE_5]))You already have the measures, so you don't need to wrap SUM around it. It should just be [Measure_1]and not sum(Fact[Measure_1])
