Top N for subcategory

[Anonymous]

 

Hello,

I have table with product,reason and values.

For each product i need to show top 5 values(measure named #m_cases). So i have written the formula for rank and added that in filter. The values are showing right but the total values are showing for only top 5 . I need to show top5 values for each product with right values and total should for all reasons. 

     I have added the below measure, it showing the right totals but values for each reason is showing same values!

ALLSELECTED = CALCULATE([#m_Cases],ALL('Cases'[Reason]))

• Kindly help. Thanks in Advance.

[Payeras_BI · Accepted solution]

Hi @Anonymous ,

I think I understood it now.

Your measure =
IF (
    ISINSCOPE ( 'Cases'[Reason] ) = TRUE (),
    [#m_Cases],
    CALCULATE ( [#m_Cases], ALL ( 'Cases'[Reason] ) )
)

Please let me know the result.

[Payeras_BI]

Hi @Anonymous ,

Try substituting ALL by VALUES.

[Anonymous]

Not working!

[Payeras_BI]

Hi @Anonymous ,

Help me to understand your expected output. From your original screenshot, which values are correct and which are incorrect?

[Anonymous]

Here is the Matrix table where i removed the top 5 filter.

 Here, For Avalara account i need the top 5 subcategory (filed name - Reason) by the value (#m_cases). I need to show the top 5 values but the total should exclude the top 5 and show for all reason. I.e 16384.