Subtotal of Weighted Scores with Filters

[Arianne]

Hello, I'm trying to pull a data card value in my power bi report that represents the sum of weighted scores that should change based on filters. I created a basic example below to show the logic.

The first table shows the type of data I am working with in excel being pushed to power bi. In the second table, when I filter my data (remove supplier F), the total weighted score stays the same, when in reality, should total what it does in the third table (which modifies the subtotals according to filters). So essentially PBI is not taking the subtotals of my filters, but just the original subtotal regardless of filtering or not. Any idea how to do this in power bi?

So when I sum the "weighted score" value in power bi, I get a value that ignores all filtering and keeps the total from the entire dataset. At the moment, my datacard value in power bi is the sum of my weighted score field.

Image #1

Image #2 (filter out supplier f that does not modify totals)

Image #3 (filter out supplier f that does modify totals)

[dedelman_clng]

Hi @Arianne - this should work for what you're trying to do

Weight =
IF (
    HASONEVALUE ( Supplier[Supplier] ),  //Value for each row
    VAR __thisInv =
        CALCULATE ( SUM ( Supplier[Invoices] ) )
    VAR __allInv =
        CALCULATE ( SUM ( Supplier[Invoices] ), ALLSELECTED ( Supplier[Supplier] ) )
    RETURN
        DIVIDE ( __thisInv, __allInv, 0 ),
    1 //Value for "Total"
)

Weighted Score =
IF (
    HASONEVALUE ( Supplier[Supplier] ),     //Value for one row
     ( [Weight] * SUM ( Supplier[Score] ) ) / 2,
    SUMX (                                  //Value for total
        ALLSELECTED ( Supplier[Supplier] ),
        [Weight] * CALCULATE ( SUM ( Supplier[Score] ) )
    ) / 2
)

With all suppliers

With F filtered out

Hope this helps

David

[Arianne]

Thank you David. It is not quite working completely. 

Perhaps I should mention that my dataset only includes the values for supplier, invoice, and score and that I need to calculate the weight and weighted score through my calculations. Below is the dataset that I am putting into PBI. Below is what I added as measures as per your suggestion but my PBI table is not depciting the right numbers (weight for supplier A should be 0.2 from 20/100, not 0.36).

Datset in PBI

Supplier	Invoice	Score
A	20	2
B	5	2
C	15	2
D	35	0
E	10	0
F	15	2

Equations used in PBI:

Weight = 
IF (
    HASONEVALUE ( 'Table'[Supplier] ),  //Value for each row
    VAR __thisInv =
        CALCULATE ( SUM ( 'Table'[Invoice] ) )
    VAR __allInv =
        CALCULATE ( SUM ( 'Table'[Invoice] ), ALLSELECTED ( 'Table'[Supplier] ) )
    RETURN
        DIVIDE ( __thisInv, __allInv, 0 ),
    1 //Value for "Total"
)

Weighted Score = 
IF (
    HASONEVALUE ( 'Table'[Supplier] ),     //Value for one row
     ( [Weight] * SUM ( 'Table'[Score] ) ) / 2,
    SUMX (                                  //Value for total
        ALLSELECTED ( 'Table'[Supplier] ),
        [Weight] * CALCULATE ( SUM ('Table'[Score] ) )
    ) / 2
)

Result in PBI:

[dedelman_clng]

Hello @Arianne -

Having Score in the visual and not having it summarized is what's causing the problem (so Now Power BI thinks it's a column not a value). This can be fixed in two ways:

1) Change the score to summarize via SUM in the drop-down list

2) Add REMOVEFILTERS(Supplier[Score]) to the Weight measure

Weight =
IF (
    HASONEVALUE ( Supplier[Supplier] ),    //Value for each row
    VAR __thisInv =
        CALCULATE ( SUM ( Supplier[Invoices] ) )
    VAR __allInv =
        CALCULATE (
            SUM ( Supplier[Invoices] ),
            ALLSELECTED ( Supplier[Supplier] ),
            REMOVEFILTERS ( Supplier[Score] )     // <----This is where it goes
        )
    RETURN
        DIVIDE ( __thisInv, __allInv, 0 ),
    1 //Value for "Total"
)

I hope this helps

David