cancel
Showing results for
Did you mean:
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 name Distinct count of Retailer code Expected Output (Average) Apr 5617 5617 May 9353 7485 June 16939 10636

Kindly help me for creating Dax.

Thank you .

5 REPLIES 5
Community Support

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.

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.

Community Support

Sorry, the original data is wrong.

I created new data:

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.

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.

Helper II

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])))))

Date                  Retailr code
 01-04-2020 0.00 RTS061120 01-04-2020 0.00 RTS061120 01-04-2020 0.00 RTE371009 01-04-2020 0.00 RT061120 01-04-2020 0.00 RT581135 01-04-2020 0.00 RT062480 01-04-2020 0.00 RT581125 01-04-2020 0.00 RT061120 01-04-2020 0.00 RT062480 01-04-2020 0.00 RT061172 01-04-2020 0.00 RT062620 01-05-2020 0.00 RT062620 01-05-2020 0.00 RT062620

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 .

Community Support

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.

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.

Helper II

hello ,

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

Announcements