cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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 other members 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 other members 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

Highlighted
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
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 139 members 1,708 guests
Please welcome our newest community members: