Using Calculatetable with summerize to get Total sales

DAX DAX Calculations
1

Dear Experts
I was creating a thread (post) few months ago: Mesure for Calculating Invoice Total Based on Order Number

the above solution work fine but the problem is the performance when used with Calculate for example:

Measure=

CALCULATE([Inv Total],
   FILTER(order_details,order_details[Order Status] = "Done"))

, I need to convert the same concept in Measure but using Calculatetable instead of Calculate to improve the performance , can anyone help me please?

Note: the data available on the mentioned thread above.

2

@Melissa are you interested in this thread?

1 Like
3

Hello @MAAbdullah47, please be reminded that while we encourage everyoneā€™s participation in the forum, we request members to refrain from single calling out of a member to answer a question as this may discourage other users to help your inquiry.

This is included in the FORUM GUIDE- Asking Questions On The Enterprise DNA Support Forum

Also, to receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Including all of the above will likely enable a quick solution to your question.

4

Hi @MAAbdullah47,

Iā€™m not sure that CALCULATETABLE will be more performant or will give you the correct answer.

I saw on the last post the measure Inv Total

 Inv Total = 
 SUMX(
     SUMMARIZE( 'Sample', 'Sample'[Order#], 'Sample'[Order Invoice Total ] ),
     [Order Invoice Total ]
)

Instead of using this measure with measure branching, maybe you could create a nee one that filters the ā€˜Sampleā€™ table in the SUMMARIZE function.

For instance, you could try something like that :

Done Total = 
SUMX(
    SUMMARIZE( 
          FILTER( order_details, order_details[Order Status] = "Done" ), 
          'order_details'[Order#], 
          'order_details'[Order Invoice Total ] ),
    [Order Invoice Total ]
)

I hope it will help you and that the performances will be better.

Best regards,
JBocher

1 Like
5

Hello @MAAbdullah47, good to see that you are having progress with your inquiry. Did the response from @JBocher help you solve your inquiry?

If it does, kindly mark as solution the answer that solved your query.

If not, how far did you get and what kind of help you need further?

6

Hi Iā€™ll check and get back to you.

1 Like
7

Thank you @JBocher It works , so you mean this way is better for performance Isnā€™t it? but can I know why?

Another question for the simple one like this:

CALCULATE([Inv Total],
FILTER(order_details,order_details[Order Status] = ā€œDoneā€))

How can make it with better performance other than (CalculateTable)?

8

Bumping this post for more visibility.

9

Hi @MAAbdullah47 - In Power BI there is a feature called Performance Analyzer. As this is a basic formula, my suggestion will be to write the required statement using different expressions as you wish and test the performance.

Thanks
Ankit J

10

Hello @MAAbdullah47, a gentle follow up if the solution provided above help solve your query?

11

Hi @MAAbdullah47,

Iā€™m not very good at explaining why it has better performancesā€¦
As @ankit said, I also use Personal Analyzer and Dax Studio to analyze my measure performances.

In the case of your measure, I saw that the Inv Total measure had a virtual table in it and I made a guess that filtering the virtual table at the source will be more performant.

I hope it will still help you and I apologize not to be able to give you more details.
My advice is if youā€™re interesting in looking at the performances of your report, find information on Performance Analyzer and above all Dax Studio :slight_smile:

Best regards,
JBocher

12

Hello @MAAbdullah47, itā€™s been a while since we got a response from you.

So a response on this post has been tagged as ā€œSolutionā€. If you have any concern related to this topic, you can create a new thread.

1 Like