Totaling Rows in matrix visual

[JacktheYeti]

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

[Anonymous]

Hi @JacktheYeti ,

Any updates?

Actually we need more details to clarify your scenario. Or share  your pbix file after removing sensitive data. You may refer to:

How to Get Your Question Answered Quickly

How to provide sample data in the Power BI Forum

Best Regards,
Eyelyn Qin

[AilleryO]

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]

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

[AilleryO]

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