Show values only at certain levels using Parameter Fields

[Andrei_Butov]

Hello,

I have two fact tables. The first table contains information on sales: revenue, date, department, and other attributes.
The second table contains planned revenue values but only for two categories: date and department.

I created Parameter Fields that include all key data dimensions.
I want to combine the revenue and plans in a Matrix table.

If I simply calculate the corresponding sums, I get a result like this:

 

 

However, since my plans exist only for date and department, I want to see blank() at all other selected levels in the hierarchy:

 

This can be easily achieved.

But what if I want to change the hierarchy, for example, by placing the store at the top level, or any other combination?
I want to see the plan only at the date and department levels and see blank() at all other levels.

[Anonymous]

Hi @Andrei_Butov ,

Create a DAX measure to conditionally display the planned revenue values only at the desired hierarchy levels. You can use the function ISINSCOPE to determine the current level of the hierarchy being displayed and conditionally return the planned revenue or a blank value.

Display Plan = 
IF(
    ISINSCOPE('DateTable'[Date]) && ISINSCOPE('DepartmentTable'[Department]),
    SUM('PlanTable'[Planned Revenue]),
    BLANK()
)

Best Regards,

Xianda Tang

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

[Andrei_Butov]

Hello @Anonymous,
your measure show results for all hierarchy levels after [date] and [department] 
Main problem: hierarchy levels can be switch 

P.S. also your measure show results only for one column [date] and [department] depending on their hierarchy. better to use ||

[Anonymous]

Hi @Andrei_Butov ,

Your idea might be feasible, through || to show the hierarchy.

After trying it, you can share your answers if you can

Best Regards,

Xianda Tang

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