Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 value...
Need Help
  • v-xiaotang's avatar
    v-xiaotang
    5 years ago

    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.

FrankAT's avatar
FrankAT
Community Champion
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    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?