Forum Discussion

JEC0210's avatar
JEC0210
Frequent Visitor
2 years ago

Filtering on PowerBI Totals

Hi all,

 

 

I have a column as part of a table visualisation, I want to filter the rows in the table such that the Total manual orders will only show the rows that sum to the "conversions to reach target". I have not been able to find a way to filter based on the total equalling a specific value, is anyone aware on how this can be done?

This is just a snippet of the manual orders, and is not the entirety of the table

 

Cheers,

JEC0210 

Need Help

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi JEC0210 ,

     

    As per your description I created these data.


    1. created index column in power query editor.


    2. create measure to get the cumulative sum and mark the rows less than or equal to the target sum as 1.

    Measure 2 =
VAR _sum = CALCULATE(SUM('Table'[Manual Orders]),FILTER(ALL('Table'),'Table'[Index] <= MAX('Table'[Index])))
RETURN
IF(_sum <= [Measure],1,0)

     

    3. Filter the data whose result is 1 in the filter.

     

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

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

    • JEC0210's avatar
      JEC0210
      Frequent Visitor
      2 years ago

      Hi Clara, 

       

      Thanks for the response. However, in my situation, manual orders is a measure, not part of any table, so creating an index is not possible as the values only appear when populating the table visual. Is there any other way to filter based on limiting rows up to when their running total equals a specific value?

       

      Cheers,

      JEC0210

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi JEC0210 ,

         

        Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

         

        Best Regards,

        Clara Gong

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