Forum Discussion
Filtering on PowerBI Totals
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.
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
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.
Hi Clara, below is some sample output, I want to limit the table visual so that the manual orders should total to 4 (the orders to convert), that is, it should only show company b and company a.
I do have this as a sample pbix file as well, but was unable to attach it, getting error file type .pbix is not supported, do you know how I could share it?
Cheers,
JEC0210
Here is some extra context behind what are measures and what are tables: