Find the minimum weight, then sum by wieght with slicers

[Anonymous]

This one is a bit hard to describe, so let me start off with showing you the data:

Group	Item	Weight	Value
1	1	5	10
1	2	6	20
2	1	2	10
2	3	7	50

The weight column is some weighting factor for the item. What I'm trying to do is to give me the sum(value) for each weight, where an item is only in 1 weight category (no duplication). I also want to be able to slice on other columns (group in this dataset).

IE if I had no slicer values selected I would see (item 1 would show up in weight 2 since that's the min):

Weight	Value
2	10
6	20
7	50

If I sliced on Group = 1 I'd see:

Weight	Value
5	10
6	20

and if I sliced on Group 2 I'd see:

Weight	Value
1	10
3	50

Is there any way to do this?

[v-jiascu-msft]

Hi @Anonymous,

Dax measure evaluates under the context. So the "weight" 5 wouldn't disappear automatically. If we add "item" to the report, it would be easier.

FinalValue =
VAR minWeight =
    CALCULATE (
        MIN ( 'Table91'[Weight] ),
        ALLEXCEPT ( 'Table91', 'Table91'[Item] )
    )
RETURN
    CALCULATE (
        SUM ( Table91[Value] ),
        FILTER ( 'Table91', 'Table91'[Weight] = minWeight )
    )

Maybe you could use the orginal data when there is a slicer.

Best Regards!

Dale

[Anonymous]

That worked great for the example dataset, but when I ran it on the full dataset, I found out that the weights are repeated in different groupings. IE I might have item 1 as a weight 1 in category A, weight 5 in B, 1 again in category C and 3 in category D. Since it sums where weight = minWeight, it sums the same item twice, once for category A and category C (both weight=1).

I've been working with something like this, which works well when showing by group, but doesn't work when graphing by weight (each weight get's a single activities hours in it if any of the categories have that item).

Corrected Value = 
sumx(
    NATURALINNERJOIN(
        SUMMARIZE(
            table, 
            table[item], 
            "min_Weight", 
            min(table[Weight])
        ), 
        SUMMARIZE(
            table, 
            table[item], 
            table[Weight], 
            table[value]
        )
    ), 
    if([Weight] = [min_Weight], [value], 0)
)

We're moving our data to redshift, which, when we change to direct queries, I think will resolve some of the issues, as I'm hoping the slicing will be passed to the server to evaluate as an additional where clause.

[Interkoubess]

Hi @Anonymous,

I don't get the selection with Group 2. (Why 1 and 3 in the weight rather than 2 and 7?)

Please elaborate and we could try to help.

Thx.