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
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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, PBIuser_CZ 

    Thank you very much for your reply, I re-modified this DAX expression based on the test file you provided, and I got the correct result:

    Garance Retail - average - Total = 
IF(
    HASONEVALUE('Date'[Month]),
    [Garance Retail - average],
    CALCULATE([Garance Retail - average],ALLEXCEPT('Date','Date'[Year])))

     

     

    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 ,

       

      EDIT: I think the subtotal for 2024 is calculating with 12 months instead of with only months with values (until current month). Do you have any idea how to catch it? 

       

       

       

       

      the result is still the same, If I count average for 2024 it should be 1,18 for the first row, not 0,59...

      The result for 2023 was correct also before but for subtotal 2024 and grand total is still incorrect...

       

      • hackcrr's avatar
        hackcrr
        Memorable Member
        2 years ago

        Hi, PBIuser_CZ 

        Perhaps you could try the following Dax expression

        Garance Retail - Corrected Average = 
IF(
    HASONEVALUE('Date'[Month]),
    [Garance Retail - average],
    AVERAGEX(
        FILTER(
            VALUES('Date'[Month]),
            NOT(ISBLANK([Garance Retail - average]))
        ),
        [Garance Retail - average]
    )
)

         

        Best Regards,

        hackcrr

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