cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

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

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

1 ACCEPTED SOLUTION
Community Support

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.

4 REPLIES 4
Anonymous
Not applicable

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

Community Support

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
Not applicable

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

Community Support

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.