Calculate Totals per Keyword


#1

Hi everyone,

Please, can someone help me with the next challenge in Power BI?

I’ve got the next table:
afbeelding

and I want to calculate the amount per keyword:

The challenge is to calculate all the amounts after a certain date to the following date with the next keyword.

Hopefully you understand what I mean by looking at the 2 examples.

When calculating in Power BI I’ve got the next report and this is not what I want :wink: :

afbeelding

Thanks in advance,

180608 Test Enterprise DNA - Amount per Keyword.xlsx (12.2 KB)

180608 BI2-89 Total Amount per Keyword.pbix (102.6 KB)


#2

Hi Cor, I see this. I will complete a good attempt on this tomorrow. There’s a bit to it actually.


#3

Cor, I’m reviewing this again and have thought about it quite a bit and keep coming to the same conclusion…

I can’t quite see how you move forward without changing the data source to fit what you need.

I don’t think there is anyway to complete this in Power BI because there just is no information which aligns to what you want

I’m looking at this table and there nothing that tells me that row 5 should be ‘A’ for example, so I’m not sure how to work in any way that logic into Power BI.

image

The same goes for the other results.

If you could get to this point with the raw data, is there any reason why you can’t also get all the data correctly worked out before bringing this into Power BI?

Unless I’m missing something big I just don’t think there is a solution for exactly what you’re asking for in this example.


#4

Thanks for your feedback Sam!

In the meantime I’ve found a solution:

Column =
IF (
    ISBLANK ( 'Table Invoices'[Keyword] ),
    CONCATENATEX (
        TOPN (
            1,
            FILTER (
                'Table Invoices',
                'Table Invoices'[Customernumber] = EARLIER ( 'Table Invoices'[Customernumber] )
                    && NOT ( ISBLANK ( 'Table Invoices'[Keyword] ) )
                    && 'Table Invoices'[Invoicedate] < EARLIER ( 'Table Invoices'[Invoicedate] )
            ),
            [Invoicedate], DESC
        ),
        'Table Invoices'[Keyword],
        ", "
    ),
    'Table Invoices'[Keyword]
)


#5

Ok nice work on this.

Is Keyword 4 a calculated column or measures?

So walking through the logic,

You are evaluating if there is a keyword, if not, then you are looking for the most recent Keyword match for a particular customer based on the invoice date.

Actually very nice way to think about it, and can honestly say I didn’t have my mind in the game to think about this ranking option.

Glad its got you what you need.

Can you scale this now from here?


#6

Keyword 4 is a calculated column, Sam.

I can scale it up, thanks for your feedback! :slight_smile:


#7

That’s great, nice one.