Counting rows with lower value

[Anonymous]

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? 

[FrankAT]

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)

[Anonymous]

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? 

[v-xiaotang]

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.

[Anonymous]

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? 

[v-xiaotang · Accepted solution]

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.

[Anonymous]

Thank you,!

[amitchandak]

@Anonymous , You can create a what if parameter and use that

Inplace of ?

selectedvalue(whatif[Param])

https://docs.microsoft.com/en-us/power-bi/desktop-what-if

[Anonymous]

Thank you for the reply. 

1. Selectedvalue function can't be used in the way you recomended. 

2. I don't understant what type of what if parameter could help in this situation.