question

VincentDialloNort-5962 avatar image
0 Votes"
VincentDialloNort-5962 asked HimanshuSinha-MSFT edited

Sales Amount per day for top 1 customer

Hello,

Hello,

I am looking to get for each day the Sales Amount for the biggest client and check how much it contributes to the total Sales. I did this code that work. This code also includes a logic to get the previous day if there isn't any data for a given day but it's very slow. Link is to a dev environment with the query.

https://dax.do/xsmPgtFoM6lbkL/




 DEFINE
     MEASURE Sales[Sales Amount Prev] =
         VAR LastDateNon =
             CALCULATE (
                 MAX ( 'Date'[Date] ),
                 filter(ALL('Date'[Date]),
                 DATEDIFF('Date'[Date],SELECTEDVALUE( 'Date'[Date] ),DAY)<5 
                 && 'Date'[Date] < SELECTEDVALUE( 'Date'[Date] ) 
                 &&NOT ISBLANK ([Sales Amount]))
             )
         VAR result =
             CALCULATE ( [Sales Amount], 'Date'[Date] = LastDateNon )
         RETURN
             if ([Sales Amount]=BLANK (),result,[Sales Amount])
      
  measure Sales[Top1CustomerWithPrev]=
          MAXX ( VALUES ( Customer[Company Name]), Sales[Sales Amount Prev] )     
  measure Sales[Top1Customer]=
          MAXX (  ALL(Customer[Company Name]), Sales[Sales Amount Prev] )     
  measure Sales[Percentage top 1 Customer]=
          Sales[Top1Customer]/[Sales Amount]  
      
 EVALUATE
 SUMMARIZECOLUMNS (
     'Date'[Date] ,
    -- Customer[Company Name],
     FILTER (All('Date'[Date]),'Date'[Date] >= DATE ( 2007, 2, 7 ) && 'Date'[Date] <= DATE ( 2008, 2, 16 ))
    ,"Total Sales Amount", [Sales Amount]
   --  ,"Previous day", Sales[Sales Amount Prev]
     ,"Sales Top 1 Customer",Sales[Top1Customer]
     ,"Percentage best customer over total",Sales[Percentage top 1 Customer]
 )
 ORDER BY 'Date'[Date]

My issue is with MAXX I can't find any other way but it creates a very slow query on a bigger dataset. Any idea to create a faster query and avoid MAXX ?

Regards

Vincent Diallo-Nort


not-supported
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @VincentDialloNort-5962 ,
Thanks for the ask and using Microsoft Q&A platform .
Since the ask is on DAX , I suggest you to please ask this question the PowerBI forum .
https://community.powerbi.com/

Thanks
Himanshu


0 Votes 0 ·

0 Answers