Using aggregated data in CALCULATE

[satishgupta]

It is my understanding that filters in CALCULATE function cannot be expressions. This is preventing me from doing a calculation.

Here are the details:

1. I have daily sales data by client.

2. I can easily get annual sales by client from this data.

3. Once I have the full year totals, I want do use CALCULATE function to sum sales for clients that exceed $100K in ANNUAL sales. CALCULATE doesn’t let me do that.

Is there a way around this?

Thank you

Satish

[Greg_Deckler]

You should be able to use something like SUMMARIZE to generate a temp table (VAR). You can then filter that table for annual sales > $100K. You could then return MAX of that table. When you put that measure in a table client you would return the correct results for each client. Still not clear on the use case exactly.

Check out my Mode measures here to see what I am talking about:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Mode-Single-Column/m-p/359150

https://community.powerbi.com/t5/Quick-Measures-Gallery/Mode-Multiple-Columns/m-p/359148

[satishgupta]

Thank you for the response.

Please note that I am a relatively novice when it comes to Power BI. I could not even make SUMMARIZE work. Two questions about that.

1. Where do I put SUMMARIZE function, (a) in the sheet that has the table with raw data, (2) in "Advanced Editor" section, or (3) on a blank tab?

2. Will the new, summary table, show up on a new tab or elsewhere?

Given my low level of expertise, if you can point me a place where I can read up on this function that would be great. I printed the function dsecription from msdn.com site, but it does not answer the kind of questions I am asking.

What I am trying to do is relatively simple.

1. Have created a model that takes daily revenue by client data.

2. I am able to create revenue by client, by year, pivot using simple techniques such as a Calendar table.

3. I am now trying to do what a SUMIF command does in Excel. I want to sum items that meet a certain criteria. It is just that I want to set it up in the model itself so that I don't have to redo the math every time I refresh data, which results in change in number of rows.  SUMMARIZE should be able to take care of this, once I am successful at getting the SUMMARIZE table.

Thank you.

Satish