cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anno2019
Helper III
Helper III

how to use Calculate function in combination with values function

Hi Guru's

 

I use the following formula which returns the "Total Pieces if Trend = "WON".

I need to add Value function so that it only looks in "Customer Name".  Not sure how to do this.

Total Pieces = 
CALCULATE(

    [pieces SUM],
    KEEPFILTERS(
        [Trend]="WON")
)
1 ACCEPTED SOLUTION

Hi @Anno2019 

 

Use SUMMARIZE('MainTable', 'MainTable'[Customer Name]) will create a new table with just the customers in. You can then add separate calculated columns to replace the measures you have. If you need to you can add aggregations in the SUMMARIZE. It will make that statement more complicated to follow. I like breaking things out into separate calculated columns so you can test each individual DAX statement.

 

See how you get on, if you get stuck let us know

 

Thanks, Brian

View solution in original post

9 REPLIES 9
Anno2019
Helper III
Helper III

@bpsearle 

Thanks for the reply.

 

The my data is on an invoice level, I need this formula to calculate @ a customer level only using the [customer name].

Not sure if that makes sense.  or is this a given and I don't have to try and aggregate at a customer level at all?

Hi @Anno2019 

 

Put it this way, if I could mock-up what you have then I could test the solution and give you the answer with an explanation as to how it works so you can build your understanding. I can’t do that without knowing what data you have and what you are trying to achieve. A screen shot of what you want and a screen shot of your data would help.

 

Thanks, Brian

@bpsearle 

Herewith a link to example data I created.

 

https://www.dropbox.com/s/98u64lzepmeab3e/Calculate%20and%20Value%20function%20use%20together.pbix?d...

Essentially, I have a measure called [Trending UP].  It uses a sumx(values( functions and works.  What I am trying to do is to replace the sumx(values with a calculate(values as I am having performance issues.  

 

Thanks for assisting me.

 

 

Hi @Anno2019 

 

OK, now I see what you mean!

 

Have you used the performance analyser view?

How many rows do you have in the main table?

 

I’m not sure that you can solve this how you want. Anything using iterators or if statements will be slower than doing it a different way. We tend to only see performance problems when the dataset gets beyond a certain size.

 

Looking at what you are doing I would be inclined to create a new calculated table that is a summary of your main table aggregated to the customer level. Then you can convert the measures you have to calculated columns so they will be processed at data load.

 

Thanks, Brian

@bpsearle 

 

My data has over 100 000 lines.

 

Ok, so aggregated calculated table, have not explored that yet.

I run through a video on how to.

Hi @Anno2019 

 

Use SUMMARIZE('MainTable', 'MainTable'[Customer Name]) will create a new table with just the customers in. You can then add separate calculated columns to replace the measures you have. If you need to you can add aggregations in the SUMMARIZE. It will make that statement more complicated to follow. I like breaking things out into separate calculated columns so you can test each individual DAX statement.

 

See how you get on, if you get stuck let us know

 

Thanks, Brian

View solution in original post

@bpsearle, thank you so much.  Will absolutely try this approach!!

Hi @Anno2019 

 

No problem, it's great being able to help 🙂

 

Thanks, Brian

 

 

bpsearle
Resolver II
Resolver II

Hey,

 

I can't quite follow what you mean.

 

Can you provide a mock up of what you want to see (screen shot of an excel representation or something similar) and a screen shot of the table with [Trend] and [customer name].

 

Thanks, Brian

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!