DISTINCTCOUNT of Transactions given FILTERS

[toddpbi]

Hello there PBI community,

I am trying to calculate the amount of unique sales documents that occur within a specific sales value of between 0<sales value<1000 on a monthly basis. But I am worried that I am not getting the correct answer. Each product is listed row by row, which means the transactions are not row by row but instead one single sales transaction can occur over multiple rows. For example, 1 sales transaction may consist of 2 products, one being 300 and the other being 650 which eventually aggregates to 950. I want to count this as 1 transaction between 0<x<1000, instead of 2 seperate transactions.

I have a calculated column:
Price Allocation =
IF (
    AND ( [BGU in LC] > 0, [BGU in LC] <=1000 ),
    "0<x<1000",
    IF (
        AND ( [BGU in LC] > 1000, [BGU in LC] <= 2000 ),
        "1000<x<2000",
        IF (
            AND ( [BGU in LC] > 2000, [BGU in LC] <= 3000 ),
            "2000<x<3000",
            IF (
                AND ( [BGU in LC] > 3000, [BGU in LC] <= 4000 ),
                "3000<x<4000",
                IF (
                    AND ( [BGU in LC] > 4000, [BGU in LC] <= 5000 ),
                    "4000<x<5000",
                    IF ( AND ( [BGU in LC] > 5000, [BGU in LC] <= 6000),
                        "5000<x<6000",
                       IF ( AND ( [BGU in LC] > 6000, [BGU in LC] <= 7000),
                        "6000<x<7000",
                        IF ( AND ( [BGU in LC] > 7000, [BGU in LC] <= 8000),
                        "7000<x<8000",
                        IF ( AND ( [BGU in LC] > 8000, [BGU in LC] <= 9000),
                        "8000<x<9000",
                        IF ( AND ( [BGU in LC] > 9000, [BGU in LC] <= 10000),
                        "9000<x<10000", BLANK()
                     
))))))))))

This column allocates each transacton within a particular sales bracket. I want to filter my data to find the amount of sales transactions that occur within 0 and 1000 sales values.

The problem is when I use DISTINCTCOUNT() is that it is removing one of sales documents.
I created a measure which counts the unique number of sales documents and then dragged months onto the axis.
=
CALCULATE ( COUNTROWS ( VALUES ( 'Table' [SalesDocuments] )
)

Any help would be appreciated on how to solve this!!!

[Floriankx]

Hello,

first of all I can highliy recommend to get familiar with SWITCH TRUE and another one to get rid of the cascaded IFs.

You want to create it as a calculated column for filtering?

The problem is if you have 1 sales transaction with to products who in Sum have another class than stand alone.

E.g two products 900 each. You want to count 1 as '1000<x<2000' if I understood correctly.

Does the first row of each transactions contain a unique identifier, because in this case you have to apply SWITCH or your IFs to an aggregated value. So either you create an aggregated table or you sum up your sales value in row of each transaction.

Can you give an example of Data for several Products per Transaction and your expected result?

Best regards.

[toddpbi]

Thank you very much for your quick response, and thank you for suggesting the use of SWITCH TRUE.

As you can see I still have a lot to learn in regards to best DAX practices.

Here is what I mean. Currently I have duplicates of sales documents within my table. In this scenario, I want to count the amount of sales documents that have a total sales value between 0 and 1000. The answer, or my expected result, would be 3 in this case (sales document 2, 3 and 1, which is 600 + 350).

Sales Document                   Sales

1                                         600

2                                         600

3                                         900

4                                         1100

5                                         1400

1                                         350

[v-yulgu-msft]

Hi @toddpbi,

Create a summarized table:

new table1 =
ADDCOLUMNS (
    SUMMARIZE (
        Transactions,
        Transactions[Sales Document],
        "total sales", SUM ( Transactions[Sales] )
    ),
    "Price Allocation", IF (
        AND ( [total sales] > 0, [total sales] <= 1000 ),
        "0<x<1000",
        IF (
            AND ( [total sales] > 1000, [total sales] <= 2000 ),
            "1000<x<2000",
            IF (
                AND ( [total sales] > 2000, [total sales] <= 3000 ),
                "2000<x<3000",
                BLANK ()
            )
        )
    )
)

Then, to get the amount of sales documents that have a total sales value between 0 and 1000, please try this measure:

count 0<x<1000 =
COUNTROWS (
    FILTER ( 'new table1', 'new table1'[Price Allocation] = "0<x<1000" )
)

Best regards,

Yuliana Gu

[toddpbi]

Hi there Yuliana-Gu,

Thank you so much.

This works well, however, would it be possible to apply this calculation without creating a summarized table?

I am sure this would be possible with a DISTINCT ( VALUES ( formula.

Any advice?

Thank you.

[v-yulgu-msft · Accepted solution]

Hi @toddpbi,

You can set the summarized table as a variable in a measure rather than generating an actual table.

Please see:

Count 0<x<1000 = var temptb=FILTER(ADDCOLUMNS(SUMMARIZE(Transactions,Transactions[Sales Document],"total sales",SUM(Transactions[Sales])) ,"Price Allocation",IF (
    AND ( [total sales] > 0, [total sales]  <=1000 ),
    "0<x<1000",
    IF (
        AND ( [total sales]  > 1000, [total sales]  <= 2000 ),
        "1000<x<2000",
        IF (
            AND ( [total sales]  > 2000, [total sales] <= 3000 ),
            "2000<x<3000",
            BLANK()                     
)))),[Price Allocation]="0<x<1000")

return COUNTX(temptb,[total sales])

Best regards,

Yuliana Gu