Forum Discussion

AgencyPowerBi's avatar
AgencyPowerBi
Helper III
8 years ago
Solved

Conditional sum calculation based on filter

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?

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

4 Replies

    • AgencyPowerBi's avatar
      AgencyPowerBi
      Helper III
      8 years ago

      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's avatar
        Anonymous
        Not applicable
        8 years ago

        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