Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Need help with Grouping Data

  Hello everybody! I can't GROUPBY my count of my purchases by count of my clients.  That's user table:               That's purchase's table:                        And that's what ...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    Hi Anonymous 

     

    try this approach.
    See the attached file as well

     

    First create a calculated table

     

    NewTable =
SELECTCOLUMNS (
    GENERATESERIES ( 1, MAX ( Purchases[Count of Purchases] ) ),
    "Count of Purchases", [Value]
)

    Then a Measure

     

    No. of users =
COUNTROWS (
    FILTER (
        VALUES ( Purchases[UserId] ),
        CALCULATE ( COUNT ( Purchases[UserId] ) )
            = SELECTEDVALUE ( NewTable[Count of Purchases] )
    )
)
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
7 years ago

Hi Anonymous 

 

In your Purchases Table, you can add this calculated column

Please see attached file with your sample data

Count of Purchases =
CALCULATE (
    COUNT ( Purchases[UserId] ),
    ALLEXCEPT ( Purchases, Purchases[UserId] )
)

Then in Table Visual, you can drag the above calculated column and DistinctCount of UserId to get the desired result

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    Hi Anonymous 

     

    try this approach.
    See the attached file as well

     

    First create a calculated table

     

    NewTable =
SELECTCOLUMNS (
    GENERATESERIES ( 1, MAX ( Purchases[Count of Purchases] ) ),
    "Count of Purchases", [Value]
)

    Then a Measure

     

    No. of users =
COUNTROWS (
    FILTER (
        VALUES ( Purchases[UserId] ),
        CALCULATE ( COUNT ( Purchases[UserId] ) )
            = SELECTEDVALUE ( NewTable[Count of Purchases] )
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Zubair_Muhammad , this is so great!:smileyhappy: But there is one thing: is there any way to filter list of users, when I click on the cell of "Count of Purchases"?

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        7 years ago

        Anonymous 

         

        I think you can establish a relationship between the columns "Count of Purchases" in Purchases and New Table

         

        Then it would work