Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Parameters Values to display ALL

I have a hierarhcy (with look-up tables) for Category and Sub-Category.

 

I want the Higher level hierarchy to show all total values for the Lower level hierarchy  values as shown below.

 

 

I can't use "sub-totals" from the matrix definitions because actual for Sales Type 1,2,3 are values and Type 4 is a Pct. (I handled this via DAX). 

 

Category -- Sales Type 1 should be Sub Category 1(Sales type 1) + Sub Category 2 (Sales type 1) etc.

 

I can achieve this by creating 2 visuals on the panel with different levels, but users want to be able to export the entire visual together.

 

Any ideas?

 

Need Help

1 Reply

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    HI, Anonymous 

    In my understanding, if you sample like this:

    Then add a hierarhcy number for hierarhcy column

     

    Then create a measure as below:

    Measure = 
IF (
    SELECTEDVALUE ( Table1[H] ) = 1,
    CALCULATE (
        SUM ( Table1[Qty] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            (
                Table1[H]
                    = MAX ( Table1[H] ) + 1
            )
                && Table1[Type] = SELECTEDVALUE ( Table1[Type] )
        )
    ),
    CALCULATE ( SUM ( Table1[Qty] ) )
)

    Result:

    If it is not your case, please share pbix file or some data sample with the data structure and expected output. You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

     

    Best Regards,

    Lin