cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rajendrabobade
Helper II
Helper II

moving average of distinct count of retailers.

Hello ,

 

I need dax of moving average of Distinct count of retailer code.

 

Example: below retailer code is apr and may (1657 & 1993 Avg =1825)

same for apr+may+june (5617 + 9315 + 16939 = 10636)

 

i need same calculation for all the month .

 

Filter = FY year.

Month nameDistinct count of Retailer code Expected Output (Average)
Apr56175617
May93537485
June1693910636



Kindly help me for creating Dax.

Thank you .

5 REPLIES 5
v-yangliu-msft
Community Support
Community Support

Hi  @Rajendrabobade  ,

Here are the steps you can follow:

1. Create calculated column.

month = MONTH('table'[Date])

2. Create measure.

Expected Output (Average) =

var _1=
MAX('table'[month])
return
AVERAGEX(
    FILTER(
        SUMMARIZE(
            ALLSELECTED('table'),
            [month],
            "Avg Value",COUNTROWS(VALUES('table'[Retailr code]))
            ),
            [month]<=_1
            ),
            [Avg Value]
            )

3. Result.

v-yangliu-msft_0-1607932788777.png

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

v-yangliu-msft
Community Support
Community Support

Hi  @Rajendrabobade  ,

Sorry, the original data is wrong.

I created new data:

v-yangliu-msft_0-1607423157751.png

1. Create calculated column.

 

month = MONTH('Table'[Date])
Month_Sales =
CALCULATE(SUM('Table'[Retailer_code]),FILTER('Table',MONTH('Table'[Date])=MONTH(EARLIER('Table'[Date]))))

 

2. Create measure.

 

Expected Output (Average) =
var _1=
MAX('Table'[month])

return
AVERAGEX(
    FILTER(
        SUMMARIZE(
            ALLSELECTED('Table'),
            [month],
            "Total Value",SUM('Table'[Month_Sales]),
            "Avg Value",AVERAGE('Table'[Month_Sales])
            ),
            [month]<=_1
            ),
            [Avg Value]
            )

 

2. Result.

v-yangliu-msft_1-1607422830356.png

You can downloaded PBIX file from here.

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you for your reply , i need distinct retailer count for each month . below is my explantion please help me on below .

 

Hello ,

 

In my  data i have  retailer code column , for calculation of moving average i need distinct count of retailers code so 

i am creating new  calculated  measure for the same like distinct count of retailer code .

 

When i am writing a dax for moving average of distinct count retailer that time my code is working but code taken distinct count mix values for all the month like . 

If april month i have Retailer code -Rts100 and my distinct count is 1 and agin for may month retailr code is same  - Rts100 and for this for distinct count is 1 so dax taken this as 1 becose we takn disticnt count ,but   i want output is = 1+1 =2 ,please help me .

 

below is my formula -

Moving Avg Retailercode = divide( CALCULATE(DISTINCTCOUNT(GRINetSalesDetailsBI[Retailer Code]),filter(allselected('Calendar'),'Calendar'[Date] <=max('Calendar'[Date]))) ,
CALCULATE(distinctcount('Calendar'[Month year]),filter(allselected('Calendar'),'Calendar'[Date] <=max('Calendar'[Date])), not(isblank((GRINetSalesDetailsBI[Retailer Code])))))
 
below is my sample data please help me to write dax .
Date                  Retailr code 
01-04-2020 0.00RTS061120
01-04-2020 0.00RTS061120
01-04-2020 0.00RTE371009
01-04-2020 0.00RT061120
01-04-2020 0.00RT581135
01-04-2020 0.00RT062480
01-04-2020 0.00RT581125
01-04-2020 0.00RT061120
01-04-2020 0.00RT062480
01-04-2020 0.00RT061172
01-04-2020 0.00RT062620
01-05-2020 0.00RT062620
01-05-2020 0.00RT062620
  
  
 
 

I want unique reatiler count of each month for moving avergae in my formula retrailr count is unique but whn i write dax that become a taken all month values unique count which is not corrcet we need each month disticn count values  to be show .

 

Thank you , Please help me .

v-yangliu-msft
Community Support
Community Support

Hi  @Rajendrabobade ,

Here are the steps you can follow:

1. Create measure.

Measure = CALCULATE(AVERAGE('Table (2)'[Distinct count of Retailer code ]),FILTER(ALLSELECTED('Table (2)'),'Table (2)'[Month name]<=MAX('Table (2)'[Month name])))

 2. Result.

v-yangliu-msft_0-1607406595433.png

You can downloaded PBIX file from here.

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

hello ,

Thank you for reply .

 

In my case retailer code is column and so i  am creating new measure like distinct count of retailer code for calculation

eg- reatiler code in column - R123 , R124 lik this i am creating distinct count as measure for calculation.

 

when i am trying your formula dax . like - calculate (average (this filed measure for me so formula cant accept measure  ). please advice 

distinct cont of reatiler is measure in my case .

Measure = CALCULATE(AVERAGE('Table (2)'[Distinct count of Retailer code ]),FILTER(ALLSELECTED('Table (2)'),'Table (2)'[Month name]<=MAX('Table (2)'[Month name])))

I hope you understand what i am traying to say .

Thank you 

 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors