calculation within matrix

[Syndicate_Admin]

Hello community!

I am trying to create an array that contains the availability of certain applications, however I have some applications that have modules, for example Microsoft:

The relationships I am applying are directly from a data specification with the general name of the application and a secondary relationship with the modules but I cannot get the measurements to bring me the correct calculations to the matrix.

cmdb ci = Microsoft

Module = Exchange, OneDrive, Sharepoint y Teams

These are the formulas that I am applying for each measure:

• Av DB Prev Month = 1 - CALCULATE(SUM('Availability DB'[Av DB Outage Availability Montly]),DATEADD('Calendar'[Date], -1, MONTH))
• Av DB MTD = 1 - SUM('Availability DB'[Av DB Outage Availability Montly])
• Av DB QTD = 1 - CALCULATE(SUM('Availability DB'[Av DB Outage Availability Montly]),DATESQTD('Calendar'[Date]))
• Av DB YTD = 1 - CALCULATE(SUM('Availability DB'[Av DB Outage Availability Yearly]),DATESYTD('Calendar'[Date]))

I am not sure if they are I must change the relationships, create hierarchies or modify the formulas so that I take into consideration the modules.

[v-kkf-msft · Accepted solution]

Hi @Syndicate_Admin ,

You need to enable secondary relationship with the modules using the USERELATIONSHIP function in your formula, like this:

Av DB Prev Month = 1 - CALCULATE(SUM('Availability DB'[Av DB Outage Availability Montly]),DATEADD('Calendar'[Date], -1, MONTH), USERELATIONSHIP('Availability DB'[Module], 'Bucket Data Specifically'[Module]))

Av DB MTD = 1 - CALCULATE( SUM('Availability DB'[Av DB Outage Availability Montly]), USERELATIONSHIP('Availability DB'[Module], 'Bucket Data Specifically'[Module]))

Av DB QTD = 1 - CALCULATE(SUM('Availability DB'[Av DB Outage Availability Montly]),DATESQTD('Calendar'[Date]), USERELATIONSHIP('Availability DB'[Module], 'Bucket Data Specifically'[Module]))

Av DB YTD = 1 - CALCULATE(SUM('Availability DB'[Av DB Outage Availability Yearly]),DATESYTD('Calendar'[Date]), USERELATIONSHIP('Availability DB'[Module], 'Bucket Data Specifically'[Module]))

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

Thanks a lot!