Forum Discussion

richard_wylde's avatar
richard_wylde
Helper IV
6 years ago
Solved

Summarized slicer

In the Slicer Advice it mentions the following limittaions :-    - The numeric range slicer filters every underlying row in the data, not any aggregated value.   Is there a way around this using ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi richard_wylde 

    For finishing this post,  we need to mark an answer as a sloution.

    So here I will copy my answer in  Dimensions filtered by summarized measure values and mark it as a solution.

    I use measure to achieve your goal.

    Measure1:

     

    Measure1 = CALCULATE(SUM(Query1[All Quotes]),FILTER(ALL(Query1),Query1[Mar_Desc]=MAX(Query1[Mar_Desc])))

     

    Measure2:

     

    Measure2 =

IF( [Measure1]<= MAX(Query1[Rank by category]),[Measure1],BLANK())

     

    We build a new table visual by Mar_Desc and Measure2. Then select 231830 in Slicer.

    Result is as below:

    You can download the pbix file from this link: Dimensions filtered by summarized measure values

     

    Best Regards,

    Rico Zhou

     

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

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi richard_wylde 

Here I have some advice. If you want to use the slicer range to be from 1 to 649021, you may try to use index column to build a slicer.

And I try to build two calculated columns to build slicers.

 

sum = CALCULATE(SUM(Query1[All Quotes]),FILTER(ALL(Query1),Query1[Mar_Desc]=EARLIER(Query1[Mar_Desc])))
Rank by category = RANKX(FILTER(Query1,Query1[Mar_Desc]=EARLIER(Query1[Mar_Desc])),Query1[Index],,ASC)

 

Result:

Rank by category will build a rank for each category in Macr_Desc column.

Sum will show the summarize for each category in Macr_Desc column.

If this reply still couldn’t help you to solve the problem, please provide me more details or show me the result visual you want.

You can download the pbix file from this link: Summarized slicer

 

Best Regards,

Rico Zhou

 

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

 

richard_wylde's avatar
richard_wylde
Helper IV
6 years ago

Thanks Anonymous  - that's great !  ( I was previously told this couldn't be done)

 

The only addition I need to make is if the All_Quotes are less than the Rank_By Category I need them to disappear rather than reduce in size,  so in this example  rank_by_category = 231830, I would need Married & Single to disappear, is this easily done ?

 

Many thanks for you help

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi richard_wylde 

    Your problem may have been solved in your new community. Link: Dimensions filtered by summarized measure values

    Please kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your problem or share me with your pbix file from your onedrive business.

     

    Best Regards,

    Rico Zhou

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi richard_wylde 

        For finishing this post,  we need to mark an answer as a sloution.

        So here I will copy my answer in  Dimensions filtered by summarized measure values and mark it as a solution.

        I use measure to achieve your goal.

        Measure1:

         

        Measure1 = CALCULATE(SUM(Query1[All Quotes]),FILTER(ALL(Query1),Query1[Mar_Desc]=MAX(Query1[Mar_Desc])))

         

        Measure2:

         

        Measure2 =

IF( [Measure1]<= MAX(Query1[Rank by category]),[Measure1],BLANK())

         

        We build a new table visual by Mar_Desc and Measure2. Then select 231830 in Slicer.

        Result is as below:

        You can download the pbix file from this link: Dimensions filtered by summarized measure values

         

        Best Regards,

        Rico Zhou

         

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