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

  • PBIuser_CZ's avatar
    PBIuser_CZ
    Frequent Visitor
    2 years ago

    Hi hackcrr,

     

    this is almost what I needed! But the Grand Total is still incorrect. I have tried to edit the code but I got still the same result. I have even no idea what it is calculating...
    The result for the first row should be 0,79 (sum of all values divided by number of months with value)

     

    Is it even possible to achieve it?