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 t...
Need Help
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.

    • PBIuser_CZ's avatar
      PBIuser_CZ
      Frequent Visitor
      2 years ago

      Hi Anonymous ,

       

      thank you for your reply. Unfortunatelly this formula does not work, the numbers are still the same

      ā€ƒ

      I am sending sample in pbix file as the original file is very complex. 

      In subtotals I would like to have average of the year and in the grand total I would like to have average of all visible values. 

      pbix sample file 

       

      Thank you very much, I appreciate your help

      • PBIuser_CZ's avatar
        PBIuser_CZ
        Frequent Visitor
        2 years ago

        Well, I think the link is not working. Can you please advice how to attach the .pbix file?
        Thank you so much