how to derive a count from a measure

[Diptarup]

I have written a measures by name Baseline Average and the value for each row item I am getting while putting them in a table is given below.

ID                      Baseline Average

A1.B1                  1

A1.B2                   0.58

A1.B3                   0.0

--------------------------

Total Average       0.55

---------------------------

However the total Average displayed is incorrect

What I intend to do is to sumup the measure "Baseline Average" and then divide it with ID#. 

Calculation would be 1.58/3 = 0.53.

How do I count the ID from the above table or there is any other way of achieving the average as 0.53. 

[v-nuoc-msft · Accepted solution]

Hi @Diptarup 

For your question, here is the method I provided:

Here's some dummy data

"Table"

Create a measure. Calculate Baseline Average.

Baseline Average = 
VAR _COUNT = 
CALCULATE(
    COUNTROWS('Table'), 
    FILTER(
        ALL('Table'), 
        'Table'[ID] = MAX('Table'[ID])
    )
)
VAR _VALUES = 
CALCULATE(
    SUM('Table'[VALUES]), 
    FILTER(
        ALL('Table'), 
        'Table'[ID] = MAX('Table'[ID])
    )
)
RETURN DIVIDE(_VALUES, _COUNT)

You can try creating the following measure.

Average of Baseline Average = 
VAR ID_COUNT = DISTINCTCOUNT('Table'[ID])
RETURN 
SUMX(VALUES('Table'[ID]), [Baseline Average]) / ID_COUNT

Here is the result.

Regards,

Nono Chen

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

[PijushRoy]

Hi @Diptarup 

Can you please try below DAX,

Measure = DIVIDE(SUM(Yourtable[Column]),COUNT(Yourtable[ID]))

If not work, please share sample data.

Let me know if that works for you

If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the Thumbs Up 👍 button if this comment helps you.

Thanks
Pijush
Linkedin