Forum Discussion

ahhollan's avatar
ahhollan
Icon for Helper I rankHelper I
3 years ago
Solved

matrix subtotal assistance

I have a table (shown below) and I want to create a matrix visual that shows the subtotal forecast by month for the Owning Org. I am new to DAX and was wondering how to do this. Currently, the matrix...
  • tamerj1's avatar
    tamerj1
    3 years ago

    ahhollan 

    now you can try

    ForecastMeasure =
SUMX (
    VALUES ( 'Date'[Month] ),
    CALCULATE (
        SUM ( 'FY22 Lending Budget'[Forecast] ),
        FILTER (
            'FY22 Lending Budget',
            MONTH ( 'FY22 Lending Budget'[Month/YR] )
                = MONTH ( MAX ( 'Date'[Calendar Date] ) )
        )
    )
)
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

ahhollan 

Which column connects the two fact table?

  • ahhollan's avatar
    ahhollan
    Icon for Helper I rankHelper I
    3 years ago

    i did have it on month/yr but changed it to owning org and that made a difference.

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      3 years ago

      ahhollan 

      now you can try

      ForecastMeasure =
SUMX (
    VALUES ( 'Date'[Month] ),
    CALCULATE (
        SUM ( 'FY22 Lending Budget'[Forecast] ),
        FILTER (
            'FY22 Lending Budget',
            MONTH ( 'FY22 Lending Budget'[Month/YR] )
                = MONTH ( MAX ( 'Date'[Calendar Date] ) )
        )
    )
)