Conditional sum calculation based on filter

[AgencyPowerBi]

Hi, 

I have a category filter

Category Target

A              All 18-20

B              All 20-25

And table in which targets are placed in columns

Data1 Data2 All 18-20  All 20-25

A           1        1              5

B            1        2             4

So how can I make a measure which calculates the sum of corresponding column (target column) depending on the Selected category?

So if pivot is filtered with Category A, I woudl get sum of All 18-20 column, and B Sum of All 20-25 column?

Is it possible?

[afzalphatan]

Can you provide pic of base data with intended result..

[AgencyPowerBi]

Well not really. 

I wanted to calculate measures in pivot in excel. That way I thought of circumventing visual basic in order to change variables in value pivot field.

I have 10 different categories, each has different tagret data for GRP calculation.

So, my idea was calculate GRP data based on category value in filter. 

I could do this through if statement in measure, but I have 40 different columns and if statement would be just too long to code.

So I wanted to change if statement with reference table, in which I would pair category with name of the column for the calculation.

If that is possible, please let me know how 🙂

[Anonymous · Accepted solution]

HI @AgencyPowerBi,

I'd like to suggest you enter to query editor and unpivot your table columns:

Then you can use above in pivot table to simply summary specific column range by filter.

Sample measures:

Sum index 18~20 = 
CALCULATE (
    SUM ( Table1[Value] ),
    FILTER (
        ALL ( Table1 ),
        Table1[Column Index] >= 18
            && Table1[Column Index] <= 20
            && Table1[Index] = MAX ( Table1[Index] )
    )
)


Sum index 20~25 =
CALCULATE (
    SUM ( Table1[Value] ),
    FILTER (
        ALL ( Table1 ),
        Table1[Column Index] >= 20
            && Table1[Column Index] <= 25
            && Table1[Index] = MAX ( Table1[Index] )
    )
)

Regards,

Xiaoxin Sheng

[AgencyPowerBi]

This is great! 

I came to this solution myself. This way I can put a slicer and change category while my values are calculated.

Thanks for this solution!