Forum Discussion

PBIuser_CZ's avatar
PBIuser_CZ
Frequent Visitor
2 years ago

Matrix totals incorrect

Hello,

 

I have a matrix with measure which is calculated average. I have years 2023 and 2024 in columns, the subtotal for 2023 is correct but subtotal for 2024 and Total is incorrect - there are the same numbers as in 2023. What I do wrong?

 

 Thank you for your help

Need Help

9 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    Hi PBIuser_CZ - Can you try  with HASONVALUE function to ensure correct calculations for subtotals and grand totals.

    AverageMeasure =
    IF (
    HASONEVALUE ( DateTable[Year] ),
    AVERAGE ( Sales[Value] ),
    AVERAGEX (
    VALUES ( DateTable[Year] ),
    CALCULATE ( AVERAGE ( Sales[Value] ) )
    )
    )

     

    replace with your existing tables and date table fields.Hope it will get resolve the issue with incorrect subtotals and totals in your matrix visual.

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

     

     

    • PBIuser_CZ's avatar
      PBIuser_CZ
      Frequent Visitor
      2 years ago

      Hi rajendraongole1 ,

       

      thank you for your reply. The thing is that the average is calculated as a number of entered cases divided by number of working days:

       

      DIVIDE(
      CALCULATE(
          COUNT('Table1'[Column_Type]),
          'Table1'[Column_Type]="Garance Retail",
          USERELATIONSHIP('Calendar'[Date],'Table1'[Date of Entry])),
          [Working days])
       
      And formula for working days is:

      Working days = NETWORKDAYS(
          MIN('Calendar'[Date]),
          MAX('Calendar'[Date]),1,'Holiday')

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Your solutions is great rajendraongole1 

        Hi, PBIuser_CZ 

        You may want to consider doing this:

        MEASURE = IF(
    HASONEVALUE (DateTable[Year]),
    [Your MEASURE of average]，
    CALCULATE ([Your MEASURE of average],VALUES (DateTable[Year]))
)

        This is an improvement over the workaround provided by rajendraongole1. 

         

         

        How to Get Your Question Answered Quickly

        If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

        Best Regards

        Jianpeng Li

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