Forum Discussion

Mente73's avatar
Mente73
Frequent Visitor
1 year ago
Solved

Countby price range

Hello,

Can anyone help me out to count the number of purchases by price/amount range?

 

I currently have a measure as follows:

PurchaseCounts =
    CALCULATE(COUNT(Transactions[Amount]),
     Transactions[Platform] = "Wallet",
     Transactions[TransactionType]="Purchase",
     Transactions[PaymentBy]<>"Lobby")
 
I need to be able to make the count segmented by amount range and expect to return something like this:

Range       CountOf Purchases
$1 - $50               50
$51- $100           101
$101 - $250         78
$251 - $500         60
$501 - $1000       12
$1000 +                2

 

Thank you iin advance for your support.

Data Protection
  • DataInsights's avatar
    DataInsights
    1 year ago

    Mente73,

     

    This approach uses a disconnected table named Segmentation:

     

     

    Create measure:

     

    Purchase Count Segmented = 
SUMX (
    Segmentation,
    COUNTROWS (
        FILTER (
            Transactions,
            Transactions[Amount] > Segmentation[Range Min]
                && Transactions[Amount] <= Segmentation[Range Max]
        )
    )
)

     

    Result:

     

     

    You can specify the additional filters on Platform, TransactionType, and PaymentBy either in filters or adding them to the FILTER function. Specifying these as filters would enable the measure to be generic and thus reflect any user-specified filters.

2 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    1 year ago

    Mente73,

     

    This approach uses a disconnected table named Segmentation:

     

     

    Create measure:

     

    Purchase Count Segmented = 
SUMX (
    Segmentation,
    COUNTROWS (
        FILTER (
            Transactions,
            Transactions[Amount] > Segmentation[Range Min]
                && Transactions[Amount] <= Segmentation[Range Max]
        )
    )
)

     

    Result:

     

     

    You can specify the additional filters on Platform, TransactionType, and PaymentBy either in filters or adding them to the FILTER function. Specifying these as filters would enable the measure to be generic and thus reflect any user-specified filters.

  • Mente73's avatar
    Mente73
    Frequent Visitor
    1 year ago

    Thank you for your time and solution, worked as expected.