Power bi matrix visual to show different calculations on drill up/down

[Anonymous]

I have a revenue table with the below columns.

Date, MSP name, revenue

I am creating two measures in my table.

No of MSP = count of unique MSP names(distinctcount(msp name)

Revenue per msp = revenue/No of MSP

I am visualizing the data in a matrix as below.

In the above visual, it shows the no of MSP, revenue per MSP on a MoM. When I drill up, measures will automatically calculate the measures on a QoQ basis. When I drill up to quarter, it will show total no of msps in that quarter and the revenue per msp is total revenue for the quarter/no of msp.

What I wanted to achieve here is, when it drills up for Q2 of 2023, no of msp should be average number of msps for Q2(Average of MSP from Apr-Jun) and the revenue per msp should be average revenue for Q2/average no of msp in Q2. It has to do the same average calculation when it drills up for year. so only for MoM, it should do the total but when drill up by quarter/year, it should do an average.

Please help me how to achieve this.

[TomMartens]

Hey @Anonymous 

you can use the function ISINSCOPE (https://dax.guide/isinscope/) checking if MoM is selected, if false then you can do something like this

AVERAGEX(
    VALUES( 'yourdatetable'[month]
    , <your measure>
)

Hopefully, this provides what you are looking for. If not, create a pbix that contains sample data but still reflects your data model (tables, relationships, calculated columns, and measures) upload the pbix to onedrive, google drive, or dropbox and share the link. Make sure that no login is required to download the pbix. If you are using Excel to create the sample data instead of the manual input method, share the xlsx as well.

Regards,

Tom

[Anonymous]

@TomMartens 

My current matrix which is month on month will have the matrix as below.

when i drill up to quarter/year, it should do an average for no of users and revenue for the months in quarter and calculate revenue per user as below for the same data.