Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Sum column value using measure as filter condition

Hi all,

 

I'm currently trying to sum up the value of a column whenever my measure DisctinctCreativeCount returns a value more than 1. The data is all based on a single table. Since I can't simply create a visual with the number I need and apply a measure as a filter to it, I've created the below measures:

 

Measure that counts how many unique Creative IDs there are:

DistinctCreativeCount = CALCULATE(DISTINCTCOUNT('BPR_DV360_Mapping'[Creative ID]),
GROUPBY('BPR_DV360_Mapping','BPR_DV360_Mapping'[DBM Line Item ID]))

 

Measure that sums up the DBM Cost (Account Currency) whenever [DistinctTest] (which always returns 1) is less than the value my first measure returns. Had to create that column as I must have a column reference in the below for the "<" operator:

CostOnOneCreative = VAR distinctive = [DistinctCreativeCount] RETURN CALCULATE(SUM('BPR_DV360_Mapping'[DBM Cost (Account Currency)]), 'BPR_DV360_Mapping'[DistinctTest] < distinctive)
 
I read around that storing your measure in a variable was a recommended approach when doing any filtering in a measure. However, the above appears to sum all rows, even though I have the 'BPR_DV360_Mapping'[DistinctTest] < distinctive condition. 
 
Let me know if you have any idea what I'm getting wrong or if I could provide any other info. Appreciate your time and help!
 
Kind regards,
Ivan
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

     

    Sorry for the delay.

    Please refer below measure to modify your measure and see if the result achieve your expectation.

    CostOnOneCreative = 
CALCULATE (
    SUM ( 'BPR_DV360_Mapping'[DBM Cost (Account Currency)] ),
    FILTER (
        ALLSELECTED ( BPR_DV360_Mapping[DBM Line Item ID] ),
        [DistinctCreativeCount] > 1
    )
)

    Result would be shown as below:

    BTW, Pbix as attached, hopefully works for you.

     

    Best Regards,

    Jay

    Community Support Team _ Jay Wang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

    Sorry for the delay.

    Please refer below measure to modify your measure and see if the result achieve your expectation.

    CostOnOneCreative = 
CALCULATE (
    SUM ( 'BPR_DV360_Mapping'[DBM Cost (Account Currency)] ),
    FILTER (
        ALLSELECTED ( BPR_DV360_Mapping[DBM Line Item ID] ),
        [DistinctCreativeCount] > 1
    )
)

    Result would be shown as below:

    BTW, Pbix as attached, hopefully works for you.

     

    Best Regards,

    Jay

    Community Support Team _ Jay Wang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Worked like a charm Jay, appreciate the help!

       

      Kind regards,

      Ivan

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

    Could you please share some sample data if you don't have any Confidential Information?

    Just look at the measure doesn't seem to see any problem.

    Best Regards,

    Jay