Forum Discussion
Counting rows with lower value
Good day,
I am trying to calculate the number of rows that have a value of 'Sąnaudos' lower than the line in question. In every line of this table it should show how many lines there are with values lower, than the value of this line.
I am trying to do it like this, but don't know how to compare value of column to the value of the same column.
No of lower values = CALCULATE(COUNTROWS('Sąnaudų lentelė'), 'Sąnaudų lentelė'[Sąnaudos] < what?
Or should I use a different DAX approach?
Hi Anonymous
thank you for your reply.
Because you have same VII ID, and property of column Sąnaudos is Sum, so it will categorize automatically according to VII ID in table visual. If you don’t need total row, you can change the property to Don’t summarize,
Result:
Or if you need total row, try this.
Create the 2 measure:
calculatesum = CALCULATE(SUM('Sąnaudų lentelė'[Sąnaudos]),ALLEXCEPT('Sąnaudų lentelė','Sąnaudų lentelė'[VII ID]))No of lower values = RANKX(ALL('Sąnaudų lentelė'),[calculatesum],,,Dense)FYI:https://docs.microsoft.com/en-us/dax/rankx-function-dax
Result:
Best Regards,
Community Support Team _ Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
8 Replies
Hi Anonymous
Not very clear your expected result, but you can take sample file attached bellow for reference.
-
Create the measure:
No of lower values = CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[Sanaudos]<SELECTEDVALUE('Table'[Sanaudos])))Result:
Best Regards,
Community Support Team _ Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Hey, thank you that was the result what I was expectecting, but as I applied it in my data setup it did not give me the same result. I am attaching a PBI desktop file for more context:
Example
What I am trying to achieve here is count the percentile of Sąnaudos (expences), for every VII ID. For that I need a count of VII that have lower value of Sąnaudos, than the selected value. Does that make sence?Hi Anonymous
thank you for your reply.
Because you have same VII ID, and property of column Sąnaudos is Sum, so it will categorize automatically according to VII ID in table visual. If you don’t need total row, you can change the property to Don’t summarize,
Result:
Or if you need total row, try this.
Create the 2 measure:
calculatesum = CALCULATE(SUM('Sąnaudų lentelė'[Sąnaudos]),ALLEXCEPT('Sąnaudų lentelė','Sąnaudų lentelė'[VII ID]))No of lower values = RANKX(ALL('Sąnaudų lentelė'),[calculatesum],,,Dense)FYI:https://docs.microsoft.com/en-us/dax/rankx-function-dax
Result:
Best Regards,
Community Support Team _ Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
I think you can do it like this:
No of lower values = VAR _MaxValue = CALCULATE ( MAX ( 'Sąnaudų lentelė'[Skirtumas] ), ALL ( 'Sąnaudų lentelė' ) ) RETURN CALCULATE ( COUNTROWS ( 'Sąnaudų lentelė' ), FILTER ( ALL ( 'Sąnaudų lentelė' ), MIN ( 'Sąnaudų lentelė'[Sąnaudos] ) <= _MaxValue && 'Sąnaudų lentelė'[Sąnaudos] > MIN ( 'Sąnaudų lentelė'[Sąnaudos] ) ) )With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)Thank you, I applied the formula "No of lover values" in the exaple file.
I am getting closer, but something in this formula is still not giving the results I was hopping for:
https://www.dropbox.com/s/jlapxus5a3i6kee/Example.pbix?dl=0
What am I missing?
Anonymous , You can create a what if parameter and use that
Inplace of ?
selectedvalue(whatif[Param])
