List of oneshot customers for a specific item

[Daro68]

Hello everyone,

I am trying to find out how I can extract a list of customers that have bought specific products with a sales amount over 15000 (regardless of the quantity), only once a year.  This is what we call oneshots.  I was able to get to the 15000 part but then I got stuck extracting the oneshots. 

In the Result column in the below visual I tried to show what I mean.  All data are in the same table so there are no relationships to be made here.

Thank you so much for your help.

[CNENFRNL]

[Daro68]

This was really going the right way, until I stumbled upon the DocumentNo what FreemanZ is asking me about.  Your solution is exactly what I need but on my mistake I did not mention the fact that there can only be one Documentnumber per year.  I'm sorry I was not clear about that.  So there is indeed an extra column in my report which is the Documentnr.

[v-yiruan-msft]

Hi @Daro68 ,

In order to get a better understanding on your requirement and give you a suitable solution for you, could you please provide some raw data in the table 'Main Table' (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Daro68]

Hello yingyinr,

Good thinking.  I have a pbix file ready for you here: https://drive.google.com/file/d/1Sa8RGRXE2TAu1sFte6we2PiYLKrJ5Rdn/view?usp=sharing

Hope you will be able to fetch it.

So if a customer in 2022 bought an item with a value of over $15.000, only once that year it is a oneshot for me. 

This is an example of what I am NOT looking for.  This customer bought an item for over $15.000 but he already bought the same item for a lower amount.  So this is not a Oneshot. 

My gratitude for your pro's is already greater than my PBI-issue right now.

[v-yiruan-msft]

Hi @Daro68 ,

I updated your sample pbix file, please check if that is what you want. You can create a calculated column as below to get it:

Column = 
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'OneshotTable'[Line Amount] ),
        FILTER (
            'OneshotTable',
            'OneshotTable'[Sell-to Customer No_]
                = EARLIER ( 'OneshotTable'[Sell-to Customer No_] )
                && 'OneshotTable'[Year] = EARLIER ( 'OneshotTable'[Year] )
                && 'OneshotTable'[No_] = EARLIER ( 'OneshotTable'[No_] )
        )
    )
RETURN
    IF ( _count > 1, "FALSE", "TRUE" )

If the above one is your expected result, please provide more special examples to explain your requirement. Thank you.

Best Regards

[Daro68]

Hello yingyinr,

It was heading the right way but unfortunately I found an error in the results.  I found a customer that bought the same item for the same amount twice, so this can not be a oneshot.

I am happy though that you were able to show me how to use the EARLIER function.  It didn't work for me previously.

But unfortunately this not the solution yet.

[Daro68 · Accepted solution]

Hello ynigynir,

I think I found the solution based on your proposal.  instead of DISTINCTCOUNT the LineAmount, I changed it to Document No.  And now the first tests are promising.

So if you are ok with it, I'll go for that solution and send you my gratitude.

[v-yiruan-msft]

Hi @Daro68 ,

Thanks for your feedback. I'm OK, please accept your last post as the solution. 😀

Best Regards

[FreemanZ]

hi @Daro68 

how does your order record table look like? Do you have something like order number?

[Daro68]

Hi there,

Yes I have an order nr-column. Normally in Excel I solve this with a concat function, but in DAX, it is not that obvious. I even tried the 'EARLIER' function but it returned an error.