Forum Discussion

JacktheYeti's avatar
JacktheYeti
Helper I
4 years ago

Totaling Rows in matrix visual

Hello experts,

 

I have a DAX measure that calculates the number of "software deployments" between the start and end year for promotions. This displays the correct totals per year, per promotion in the matrix visual correctly (See First screenshot). I encounter an issue when I drill up one level to ascertain the totals for each Year (see screenshot 2)

 

The numbers I'm expecting to see are below. 

DAX Measures:

Software Deployments =
[Percentage of Software Redeemed per Year]
* CALCULATE (
[Software Deployed by DateDim],
ALLSELECTED ( 'Date Dimension'[Date].[Year] )
)
 
Percentage of Software Redeemed per Year = DIVIDE(SUM(SoftwareRedemptionTable[RedemptionCount]),CALCULATE(SUM(SoftwareRedemptionTable[RedemptionCount]), ALLSELECTED('Date Dimension'[Date].[Year])))
 
Software Deployed by DateDim =
CALCULATE(SUM(SoftwareDeployedTable[TotalDeployedCount]), USERELATIONSHIP('Order'[ApprovedDate],'Date Dimension'[Date]))
Need Help

4 Replies

  • AilleryO's avatar
    AilleryO
    Memorable Member
    4 years ago

    Hi,

     

    Maybe you should try using a date table, marked as a date table. I know you table is not marked because you are using the .Year or .Month, but those extensions should not be there if your table is marked as date table.

     

    More explanations here :

    https://www.sqlbi.com/articles/mark-as-date-table/

     

    Instead of those extensions, you should use the Year Column of your date table, or Month column.

     

    Hope it make sense otherwise do not hesitate to ask for more 😉

    • JacktheYeti's avatar
      JacktheYeti
      Helper I
      4 years ago

      Thanks, that's really good to know, however even after marking the table as a date table, the totals remain the same. 🙂

      • AilleryO's avatar
        AilleryO
        Memorable Member
        4 years ago

        Hi,

         

        Can you tell us which formula you are using, since the old one must have been in error after marking the table as date table.

        Talk to you soon