Results 1 to 3 of 3

Thread: Add yearly total to chart split by quarters
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    North Yorkshire, UK
    Posts
    263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Add yearly total to chart split by quarters

    I've just discovered Power BI for desktop (literally two days ago) and am enjoying playing around with it, but I've come across a charting problem - something that I could easily do in Excel that I can't figure out how to do in Power BI.

    I have a table of sales data showing quarter number (Q1 to Q4), financial year (FY17 and FY18) and the sales figure for each quarter.

    I've created a clustered column chart listing each quarter along the x-axis, and against each quarter, there are two columns showing the total sales for each quarter, one for FY17 and one for FY18. So far so good...

    What I'm now trying to do is to add another "data point" along the x-axis to show total sales which will again have the two columns for each financial year. The final chart needs to have 5 data points along the x-axis (Q1, Q2, Q3, Q4, TOTAL), and against each point, have two columns, the first showing FY17 sales and the second showing FY18 sales.

    I can't figure out how to add in the data point and columns for the total sales values. I've ordered a couple of books so I can delve into Power BI in more detail, but I could really do with a solution to this problem before the books arrive.

    Any help gratefully appreciated.
    It's curtains for your Dr. Horrible ... lacy, gently wafting curtains.

  2. #2
    MrExcel MVP VBA Geek's Avatar
    Join Date
    Dec 2013
    Location
    Kepler 438B
    Posts
    2,857
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Add yearly total to chart split by quarters

    You'd need to create an additional table with this DAX query:

    Table =
    ADDCOLUMNS (
        SUMMARIZECOLUMNS (
            Data[Year],
            ROLLUPADDISSUBTOTAL ( Data[Quarter], "QuarterSubtotal" ),
            "Total"SUM ( Data[Value] )
        ),
        "NewQuarter"IF ( [QuarterSubtotal], "TOTAL", [Quarter] )
    )



    and then you can build the graph:

    Excel and BI blog: http://xcelanz.com/

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    North Yorkshire, UK
    Posts
    263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add yearly total to chart split by quarters

    Thanks for the info. I don't think I'd ever have worked that out for myself!

    I've got this to work with some sample data, now to try it for real.

    I've got a lot of Excel knowledge, but this whole DAX thing is completely new to me. Time to do some reading and watch some YouTube tutorials I think.
    It's curtains for your Dr. Horrible ... lacy, gently wafting curtains.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •