average of distinct count (promedio de la suma de un distinct count)

[Anonymous]

tengo esta tabla que muestra un distinct count de un campo con formato texto (string) de los últimos 7 días

Recuento distintivo de sth	fecha
496	01/07/2022 00:00
429	02/07/2022 00:00
1	03/07/2022 00:00
1	04/07/2022 00:00
428	05/07/2022 00:00
420	06/07/2022 00:00
1	07/07/2022 00:00

Quisera obtener una medida donde resulte el promedio de los dístinct count por día y este cambie de acuerdo a los útlimos 7 días. 

el resultado a obtener es el siguiente: 

Gracias por sus comentarios

[Anonymous]

@v-zhangti 

One question, how do I incorporate the distinct count rule into the measure?. I do not have the added field of the count distinction, I have the field that is in text format like this:

by the way, the table is in direct query

[v-zhangti]

Hi, @Anonymous 

Are you trying to get a result like this？

Measure = DISTINCTCOUNT('Table'[Text])

Best Regards,

Community Support Team _Charlotte

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

[Anonymous]

I do not have the summarized table like this:

I have the table like this: 

So, how do I incorporate the distinct count rule into the measure?

DISTINCTCOUNT('Table'[Text])

in the dax measure

Last 7-day average =
VAR N1 =
    SUMMARIZE (
        FILTER ( ALL ( 'Table' ), [fecha] <= MAX ( 'Table'[fecha] ) ),
        [fecha],
        "Sum", SUM ( 'Table'[Recuento distintivo de sth] )
    )
VAR N2 =
    TOPN ( 7, N1, [fecha], DESC )
RETURN
    SUMX ( N2, [Sum] / 7 )

Thanks

[v-zhangti · Accepted solution]

Hi, @Anonymous 

The text format of numbers can be directly converted to integer format.

Measure:

Last 7-day average =
VAR N1 =
    SUMMARIZE (
        FILTER ( ALL ( 'Table' ), [fecha] <= MAX ( 'Table'[fecha] ) ),
        [fecha],
        "Sum", SUM ( 'Table'[Recuento distintivo de sth] )
    )
VAR N2 =
    TOPN ( 7, N1, [fecha], DESC )
RETURN
    SUMX ( N2, [Sum] / 7 )

Always calculate the average of the latest 7 days.

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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