1

For a scorecard, I need to weigh some categories based on importance. Said weights must be selectable from a filter based on their category (categories might have different weights).

I created a table with the weights with the percentages to multiply against and created the below:

CAT3 Selection

cat3 Selection =
IF (
    SUM ( Query1[CAT3 Error] ) = BLANK (),
    BLANK (),
    IF (
        HASONEVALUE ( 'Weighted Percents'[CAT3 Weight] ),
        VALUES ( 'Weighted Percents'[CAT3 Weight] ),
        0
    )
)

CAT4 Selection

cat4 Selection =
IF (
    SUM ( Query1[CAT4 Error] ) = BLANK (),
    BLANK (),
    IF (
        HASONEVALUE ( 'Weighted Percents'[CAT4 Weight] ),
        VALUES ( 'Weighted Percents'[CAT4 Weight] ),
        0
    )
)

cat3 Weighted Scenario

( [cat3 Selection] * 1 ) * SUM ( Query1[cat3 Error] )

cat4 Weighted Scenario

( [cat4 Selection] * 1 ) * SUM ( Query1[cat4 Error] )

My problem is this: When I select a slicer, it works, but it also automatically selects the same value on the other slicer. If I edit the interactions between the two slicers, I get a value of 0 in the data unless they are the same. How can I fix this?

Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
Btriggered
  • 11
  • 3
  • Does the `Weighted Percents` table have a row for each CAT3 and CAT4 weight combination? If not, that's your problem. Having two slicers on the same table doesn't work well if you expect them to be independent. – Alexis Olson Dec 11 '20 at 23:42
  • Hi, yes, they do. I did try different tables but got the same results. Would having the results on separate tables as well maybe fix this or is it a value() issue? – Btriggered Dec 12 '20 at 14:07

2 Answers2

0

This is a rather convoluted way of harvesting slicers. A more standard way to write things would be like

cat3 weighted =
SELECTEDVALUE ( 'Weighted Percents'[CAT3 Weight] ) * SUM ( Query1[cat3 Error] )

cat4 weighted =
SELECTEDVALUE ( 'Weighted Percents'[CAT4 Weight] ) * SUM ( Query1[cat4 Error] )

If this simplified construction still has the same problem, then I would recommend using independent tables for your slicer selections rather than having just the one.

Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
0

Just needed to remove the relationship of the duplicated weight tables and use multiple tables

Btriggered
  • 11
  • 3