cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
IvanIvanov Frequent Visitor
Frequent Visitor

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
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
v-jayw-msft Senior Member
Senior Member

Re: Sum column value using measure as filter condition

Hi @IvanIvanov ,

 

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:

1.PNG

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.

View solution in original post

4 REPLIES 4
v-jayw-msft Senior Member
Senior Member

Re: Sum column value using measure as filter condition

Hi @IvanIvanov ,

 

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

IvanIvanov Frequent Visitor
Frequent Visitor

Re: Sum column value using measure as filter condition

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

Highlighted
v-jayw-msft Senior Member
Senior Member

Re: Sum column value using measure as filter condition

Hi @IvanIvanov ,

 

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:

1.PNG

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.

View solution in original post

IvanIvanov Frequent Visitor
Frequent Visitor

Re: Sum column value using measure as filter condition

Worked like a charm Jay, appreciate the help!

 

Kind regards,

Ivan

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)