Forum Discussion

Andrei_Butov's avatar
Andrei_Butov
Regular Visitor
2 years ago

Show values only at certain levels using Parameter Fields

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.

Interesting Links
Need Help
Show and Tell
Tips and Tricks

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Andrei_Butov
      Regular Visitor
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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.