Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Median Calculation different on Card Visual and Clustered Column Index

Hello world! 

I'm unable to get around this issue and wanted to post it here to see if the community can provide some answers.

I have a star schema data model, where the Measure sits in the Fact table. On the dashboard, all 8 filters come from dimension tables 
The Measure in question calculates the MEDIAN of a column after the filters are applied. This meausure is used on two visuals 
1) Card Visual
2) Clustered Column Visual

Card Visual has only one field and that is the Measure(which calculated the median)

Clustered column has the same measure and on the x-axis also has the column coming from one of the dimension tables to seggrate by location. 

Here is the formula - 

Measure = MEDIAN(FACT_TEST[collection_to_repo_minutes])/60
 
I pulled the raw data from the db using the same filters and looks like the card visual provides the accurate value, while the clustered column visual provides the median value after removing the duplicates(which is not accurate)

Does anyone know why this is happening or how we can fix it?
 

 



 




 

 






Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

     

    Does [Local...Area Name] column from FACT_TEST table or other related tables? 

    As far as I know, In Clustered Column Visual or Table visual, the measure will return result based on the area name, and in subtotal, it will return result based on whole table. This is caused by your data model.

    If you want 1.3 as result in your report, you can try removefilters function.

    Measure =
CALCULATE (
    MEDIAN ( FACT_TEST[collection_to_repo_minutes] ),
    REMOVEFILTERS ( 'TableName'[Local...Area Name] )
) / 60

     

    Best Regards,
    Rico Zhou

     

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thanks for looking into this Rico.

    To clarify, [Local...Area Name] is coming from another dimension table.

    What more is found out is that, when I change fact_table from direct query mode to import mode, the values match.