Calculate measure only for total column of quarter in quarter month hierarchy

[Anonymous]

I have two tables I'm working with. One that contains the actuals per month and one that has the forecasted opportunities per quarter. The tables are joined on the quarter.

I created a hierarchy on the actuals table for the quarter and month. I have a measure on the opportunities table that is the sum of the opportunities that are open.

It looks like this right now (data blanked out). because the tables are joined on the quarter and I'm showing months. The value for Most Likely 3 (opportunities) repeats for each month. Is there a way to only show it on the total column?

Thanks!!

Sean

[v-jingzhang · Accepted solution]

Hi @Anonymous 

You can use ISINSCOPE function to tell whether it is in a specific hierarchy or not. 

If the "Most Likely 3" is a measure, you could try creating a measure like below to replace it in the matrix. 

new Most Likely 3 = IF ( ISINSCOPE ( 'table'[month] ), BLANK (), [Most Likely 3] )

If the "Most Likely 3" is a value in a column, try

measure =
IF (
    SELECTEDVALUE ( 'table'[column] ) = "Most Likely 3",
    IF ( ISINSCOPE ( 'table'[month] ), BLANK (), [measure for column value] ),
    [measure for column value]
)

If this doesn't help, can you provide some dummy data which we can use to create a simple model?

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[Ashish_Mathur]

Hi,

Share the 2 Tables and show the expected result.

[lbendlin]

use AVERAGE() aggregation for the forecast, or use ROLLUP functions  to influence the visibility of the measure result. ROLLUPGROUP function (DAX) - DAX | Microsoft Docs