Average of Distinctcount over time

[tqn626]

I have this code that shows me the disctinctcount of stores with any sales. I'm using this in a calendar visual. How would I get the average distinct count at each calendar category. In the visual on the bottom i want the monthly to show 19 stores instead of 39.

Stores =
CALCULATE (
    DISTINCTCOUNT ( DimStore_List[Store] ),
    FILTER ( 'Daily Data', 'Daily Data'[Net_Sales] > 0 )
)

[amitchandak]

@tqn626 , Based on the group level of Day or month then create a measure like

Stores =
var _1 =
CALCULATE (
DISTINCTCOUNT ( DimStore_List[Store] ),
FILTER ( 'Daily Data', 'Daily Data'[Net_Sales] > 0 )
)
return
sumx(values(Date[Date]),_1)

[tqn626]

Hmm, This didn't work, I got a much larger number.

[v-deddai1-msft · Accepted solution]

Hi @tqn626 ,

You can try the following measure:

Stores =
IF (
    ISINSCOPE ( the_hierarchy_in_fw_quarater ),
    CALCULATE (
        DISTINCTCOUNT ( DimStore_List[Store] ),
        FILTER ( 'Daily Data', 'Daily Data'[Net_Sales] > 0 )
    ),
    AVERAGEX (
        DimStore_List,
        CALCULATE (
            DISTINCTCOUNT ( DimStore_List[Store] ),
            FILTER ( 'Daily Data', 'Daily Data'[Net_Sales] > 0 )
        )
    )
)

If you need accurate formula, please show us sample data.

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

Best Regards,

Dedmon Dai