Sum column value using measure as filter condition

[Anonymous]

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

[v-jayw-msft · Accepted solution]

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]

Worked like a charm Jay, appreciate the help!

Kind regards,

Ivan

[v-jayw-msft]

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

[Anonymous]

Hi @v-jayw-msft ,

Thanks for looking into this! You can find sample data below:

https://docs.google.com/spreadsheets/d/1l0Le0-NHr23-3OGIZ6SaojLmMNh0nCSEqKKyd5BtsK0/edit?usp=sharing

It's a snippet of the main data.

Appreciate the help!

Kind regards,

Ivan