cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
corvada Helper III
Helper III

Calculate Totals per Keyword

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 1 (12.2 KB)

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

3 ACCEPTED SOLUTIONS

Accepted Solutions
Super User III
Super User III

Re: Calculate Totals per Keyword

@corvada

 

Try this calculated column

 

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]
)
Try my new Power BI game Cross the River

View solution in original post

Super User III
Super User III

Re: Calculate Totals per Keyword

@corvada

 

Sorry for late reply.

 

It works like this

 

1) The Filter part filters the table and grabs only rows with a) same customer number b) non blank key words c) where date is before the current row date

 

FILTER (
                'Table Invoices',
                'Table Invoices'[Customernumber] = EARLIER ( 'Table Invoices'[Customernumber] )
                    && NOT ( ISBLANK ( 'Table Invoices'[Keyword] ) )
                    && 'Table Invoices'[Invoicedate] < EARLIER ( 'Table Invoices'[Invoicedate] )
            )

 

2) TOPN filters this (already filtered) table further and grabs the row with the maximum date

 

3) from above steps we have a single row of all the Columns in the Table...But we need only "Key Word" column. So I used an ITERATOR to grab it...We could also have used other ITERATORS like MINX or MAXX.....or other techniques like selectcolumns as well

 

Try my new Power BI game Cross the River

View solution in original post

corvada Helper III
Helper III

Re: Calculate Totals per Keyword

Thank you very much, @Zubair_Muhammad, it clear things up for me. Thumbs up, 🙂

View solution in original post

11 REPLIES 11
Super User III
Super User III

Re: Calculate Totals per Keyword

HI @corvada

 

Try this MEASURE

 

Measure =
VAR mydate =
    MAX ( 'Table Invoices'[Invoicedate] )
VAR nextnonblankdate =
    CALCULATE (
        MIN ( 'Table Invoices'[Invoicedate] ),
        FILTER (
            ALLEXCEPT ( 'Table Invoices', 'Table Invoices'[Customernumber] ),
            'Table Invoices'[Invoicedate] > mydate
                && NOT ( ISBLANK ( 'Table Invoices'[Keyword] ) )
        )
    )
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( SELECTEDVALUE ( 'Table Invoices'[Keyword] ) ), BLANK (),
        ISBLANK ( nextnonblankdate ), SUM ( 'Table Invoices'[Amount excl VAT] ),
        CALCULATE (
            SUM ( 'Table Invoices'[Amount excl VAT] ),
            FILTER (
                ALLEXCEPT ( 'Table Invoices', 'Table Invoices'[Customernumber] ),
                'Table Invoices'[Invoicedate] >= mydate
                    && 'Table Invoices'[Invoicedate] < nextnonblankdate
            )
        )
    )
Try my new Power BI game Cross the River
Super User III
Super User III

Re: Calculate Totals per Keyword

@corvada

 

Please see attached file as well

 

keywords.png

Try my new Power BI game Cross the River
corvada Helper III
Helper III

Re: Calculate Totals per Keyword

@Zubair_Muhammad

 

Thanks for your feedback!

 

Your formula gives the following result:

PBI1.PNG

 

The correct answer should be:

 PBI2.PNG

 

Maybe there is also a solution in Power Query? 

Can you give it another try?

 

Thanks in advance,

 

Super User III
Super User III

Re: Calculate Totals per Keyword

@corvada

 

Try this calculated column

 

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]
)
Try my new Power BI game Cross the River

View solution in original post

Super User III
Super User III

Re: Calculate Totals per Keyword

@corvada

 

kw.pngkw2.png

Try my new Power BI game Cross the River
Super User III
Super User III

Re: Calculate Totals per Keyword

@corvada

 

File attached as well

Try my new Power BI game Cross the River
corvada Helper III
Helper III

Re: Calculate Totals per Keyword

Fantastic, @Zubair_Muhammad, you've made my day! Thank you very much! 

 

 

corvada Helper III
Helper III

Re: Calculate Totals per Keyword

@Zubair_Muhammad,

 

I am trying to understand your calculated column-formula. 

Can you explain exactly what you did? 

I want to learn from it. 🙂 

corvada Helper III
Helper III

Re: Calculate Totals per Keyword

This part:

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],
        ", "
    ),

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors