Matrix not showing correct total for measure, how to get it show as average?

[Anonymous]

Good morning Powerbi family,

i created a measure to calculate a ratio between two columns ( with filters built in )

when i put it against the data, it shows correctly on each row.

but on the total it shows a number that looks like an average, but it is actually not .

i have calculated the average, and it is not the number shown on the Totals (1.04).

I have look at other thread, but i couldn't find a similar problem because the way my measure was setup.

can anyone help me with this one?

[v-stephen-msft]

Hi @Anonymous ,

You could create a new measure like:

Average =
IF (
    ISINSCOPE ( 'TableName'[HKDATE] ),
    [reh/LS ALL LSWS Transfer OUT moves],
    DIVIDE (
        SUMX ( 'TableName', [reh/LS ALL LSWS Transfer OUT moves] ),
        CALCULATE ( DISTINCTCOUNT ( 'TableName'[HKDATE] ), ALLSELECTED ( 'TableName' ) )
    )
)

If you still have problems, please provide your sample data, and pay attention to protecting privacy.

Best Regards,

Stephen Tao

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

[amitchandak]

@Anonymous , Distinct count is recalculated for grand total, so that number is not the sum of rows values. Hope you are not trying to sum the denominator and check?

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

@amitchandak 
I see,
how do i get the grand total to show average instead ?
because i need to show the average over a period of time as a KPI