Forum Discussion

sebouier's avatar
sebouier
Frequent Visitor
3 years ago

Asynchronous matrix table wrong subtotal

Hi,

 

For my client, I had to develop an asynchronous matrix table : 

I built my measure to show in the subtotal row the evolution (in %) between the minimum year and the maximum year.

When I have data for each year to compare (one row for 2021 vs one row for 2020), the subtotal is correct.

When I don't, the subtotal is false.

The expected subtotal would be 100% if no row "2020", -100% if no row "2021"

 

Here's one of the measure I use : 

NbMandAllType with Evol = 
    IF(HASONEVALUE('Calendar'[Date].[Year]),
        [NbMandAllType],
        FORMAT([Evol all type between A min and A max],"+0\ %;-0\ %;+0\ %")
    )

 

I undersdand why I get what I get. When I have only Year row, the condition "HASONEVALUE('Calendar'[Date].[Year])" becomes true for the subtotal row as well. 

 

But how to fix it ? I think I have to add another condition in my IF, maybe a Year row count, but I can't get it right.

 

Thank you very much for your help.

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    hi sebouier 

    what if you just write the measure like?

    NbMandAllType with Evol =
    FORMAT([Evol all type between A min and A max],"+0\ %;-0\ %;+0\ %")
    )

    • sebouier's avatar
      sebouier
      Frequent Visitor
      3 years ago

      Hi FreemanZ, 

       

      If I do that, my table will contain only pourcentage values, the subtotal value would be correct but not the year row values.