Dividing 2 Measures Displays Unexpected Results

[JacktheYeti]

Hello,

I have a series of measures I use to calculate different metrics for software deployment and downloads.

I am trying to calculate a Participation Rate Percentage by diving two of those measures (The Software Redeemed Divided by Deployed Software per Year). The table visual displays the expected results for each of these calculations except for the percentage calculation. I'm not sure what I've missed, any help would be greatly appreciated.

Year	Software Redeemed	Deployed Software per Year	Participation Rate (Current Results)	Participation Rate (What I'd expect / want to happen)
2018	186,377	1,137,343	16%	15%
2019	229,983	1,403,443	16%	12%
2020	404,850	2,470,548	16%	20%
2021	158,633	968,038	16%	19%

Deployed Software per Year DAX

Deployed Software per Year = [Percentage of Software Keys Redeemed per Year]*CALCULATE([Keys Deployed by DateDim], ALLSELECTED('Date Dimension'[Date].[Year]))

The two measures referenced in the above DAX measure above

Percentage of Software Keys Redeemed per Year = DIVIDE(SUM(SoftwareKeyDailyRedemptions_WithStatus[DailyRedemptionCount]),CALCULATE(SUM(SoftwareKeyDailyRedemptions_WithStatus[DailyRedemptionCount]), ALLSELECTED('Date Dimension'[Date].[Year])))

Keys Deployed by DateDim = CALCULATE(SUM(SoftwareKeyCounts[TotalSoftwareKeysCount]), USERELATIONSHIP('Order'[ApprovedDate],'Date Dimension'[Date]))

 Percentage DAX:

Participation Rate (Software Keys Redeemed / Software Keys Deployed) = DIVIDE(sum(SoftwareKeyDailyRedemptions_WithStatus[DailyRedemptionCount]), [Deployed Software per Year],0)

[v-yangliu-msft]

Hi  @JacktheYeti ,

The Participation Rate in your text is through The Software Redeemed Divided by Deployed Software per Year:

If this is the case, your result is correct.

But the Participation Rate in your formula is again calculated by sum(SoftwareKeyDailyRedemptions_WithStatus[DailyRedemptionCount]) Divided by [Deployed Software per Year].

I am not very sure what the data of SoftwareKeyDailyRedemptions_WithStatus[DailyRedemptionCount]) looks like

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JacktheYeti]

HI Liu Yang,

I'm sharing a file with the sensitive data removed to help illustrate my issue. This is exported from a matrix visual with the measures described above. For each field, the measures calculate the correct software downloads, deployed software per year, and participation rate per promotion. (The promotional name is sensitive so I removed it. I've added a BLANK row between promotions to seperate).

Starting on ROW 425 is a "Totals Table" that contains the results I'd expect. Is it possible to apply this same logic, per year, in the Totals Table to the table in PowerBI?

[JacktheYeti]

Hello! Did the shared file help at all? Still searching for an answer if there is one 🙂