topic Re: Average Days Traded for Regions in DAX Commands and Tips

Average Days Traded for Regions

Bopps — Mon, 10 May 2021 07:21:52 GMT

Hi there,

Please help 🙂

I need to get the average days traded by region in my dashboard.

Previously I used to distinct coiiunt the days that each terminal traded anfd then export to excel and average per region. This has become tedious as i now ahve multiple countries with multiple regions,

I am still a newbie to this asll so I attempted to right something to help, the below still returns the average days per terminal, which is great, but now I need this to return per region.

Days Traded Dax =

SUMX (

SUMMARIZE (

'Sales by TPM',

'Sales by TPM'[TPMNUMBER],

"Days Traded", DISTINCTCOUNT ( 'Sales by TPM'[DATE] )

[Days Traded]

)

Re: Average Days Traded for Regions

amitchandak — Mon, 10 May 2021 07:58:10 GMT

Bopps , You can try a measure like

AverageX (
SUMMARIZE (
'Sales by TPM',
'Sales by TPM'[region],
"Days Traded", DISTINCTCOUNT ( 'Sales by TPM'[DATE] )
),
[Days Traded]
)

correct the table name for region

Re: Average Days Traded for Regions

Bopps — Mon, 10 May 2021 08:23:39 GMT

Thanks, ive tried this.

It seems to be returning the maximum though, the full number of days in the month.

I pulled the data into excel to see the true average,

Average for April 2021 should be 27,35

Not sure what isnt working?

Could this be that the terminal is appearing for multiple products?

This should matter becuase we are doing disticnt count for that terminal for the day?

Re: Average Days Traded for Regions

PaulOlding — Mon, 10 May 2021 11:24:39 GMT

Perhaps this measure is what you want...

Average Days per Terminal =

VAR _UniqueTerminalDays =

SUMMARIZE('Sales by TPM',

'Sales by TPM'[TPMNUMBER],

'Sales by TPM'[DATE]

)

VAR _Numerator = COUNTROWS(_UniqueTerminalDays)

VAR _Denominator = COUNTROWS(VALUES('Sales by TPM'[TPMNUMBER]))

VAR _Result = DIVIDE(_Numerator, _Denominator)

RETURN

_Result

Re: Average Days Traded for Regions

Bopps — Mon, 10 May 2021 13:31:29 GMT

Thanks Paul,

Ill give this a try too,

I had a work around that gave the aqverage dayts traded by subsituting the summarise from regions to by Termninal.

A bit slower but is working 🙂

You guys are amazing!!