cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
siemdevlieger Frequent Visitor
Frequent Visitor

Create summary table with qtd values (and still showing months in column)

Hi community!

 

I am struggling with creating a summary table showing me qtd values on monthly records.

I know how to do it with measures, but am really looking to have a table with these details, so I can use this table again for other things. The base table that I use contains a huge amount of columns, the summary table should look somewhat like below:Screenshot Power BI.JPG

I tried to achieve it with the summarize and add column function, but I only get the sales per month. I don't succeed in getting the correct QTD values.

 

Someone who tried this before and might have an example formula with some explanation?

 

Thanks in advance!

Siem

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Create summary table with qtd values (and still showing months in column)

Hi @siemdevlieger ,

 

Have a try 🙂

 

QTD SALES = 
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Year] = EARLIER ( 'Table'[Year] )
            && 'Table'[Month] <= EARLIER ( 'Table'[Month] )
            && 'Table'[Product group] = EARLIER ( 'Table'[Product group] )
            && 'Table'[Region] = EARLIER ( 'Table'[Region] )
    )
)

Capture.PNG

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: Create summary table with qtd values (and still showing months in column)

Hi @siemdevlieger ,

 

Have a try 🙂

 

QTD SALES = 
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Year] = EARLIER ( 'Table'[Year] )
            && 'Table'[Month] <= EARLIER ( 'Table'[Month] )
            && 'Table'[Product group] = EARLIER ( 'Table'[Product group] )
            && 'Table'[Region] = EARLIER ( 'Table'[Region] )
    )
)

Capture.PNG

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

siemdevlieger Frequent Visitor
Frequent Visitor

Re: Create summary table with qtd values (and still showing months in column)

Thanks Frank!

 

I have reviewed the formula in your dataset and have made a small addition as it was now giving YTD sales instead of QTD sales. For that I also added the 'Quarter' column in the filter of your formula. It give the exact results then as in my initial question.

 

Power BI QTD sales.JPG

 

I will work tomorrow / Wednesday on implementing this in my own dataset, but have a good feeling about this solution!

 

Will get back to you once implemented with any issues I might encounter!

 

Regards,

Siem

siemdevlieger Frequent Visitor
Frequent Visitor

Re: Create summary table with qtd values (and still showing months in column)

Thanks again Frank,

 

I have implemented this part now in my Dashboard and it works exactly as it should!

It was a crucial part for some customized calculations in my Dashboard and was giving me a bad headache in the last weeks to get this solved 🙂

 

Best regards,

Siem

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors