Average Days Traded for Regions

[Bopps]

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]

)

[amitchandak]

@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

[Bopps]

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? 

[PaulOlding · Accepted solution]

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

[Bopps]

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!!