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 ...
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

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

  • AgencyPowerBi's avatar
    AgencyPowerBi
    Helper III
    8 years ago

    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!