Add yearly total to chart split by quarters

JugglerJAF

Active Member
Joined
Feb 17, 2002
Messages
264
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.
 

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
You'd need to create an additional table with this DAX query:

Table =<br><span class="Keyword" style="color:#0070FF">ADDCOLUMNS</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">SUMMARIZECOLUMNS</span><span class="Parenthesis" style="color:#969696"> (</span><br>        Data[Year],<br><span class="indent8">        </span><span class="Keyword" style="color:#0070FF">ROLLUPADDISSUBTOTAL</span><span class="Parenthesis" style="color:#969696"> (</span> Data[Quarter], <span class="StringLiteral" style="color:#D93124">"QuarterSubtotal"</span> <span class="Parenthesis" style="color:#969696">)</span>,<br><span class="indent8">        </span><span class="StringLiteral" style="color:#D93124">"Total"</span>, <span class="Keyword" style="color:#0070FF">SUM</span><span class="Parenthesis" style="color:#969696"> (</span> Data[Value] <span class="Parenthesis" style="color:#969696">)</span><br><span class="indent4">    </span><span class="Parenthesis" style="color:#969696">)</span>,<br><span class="indent4">    </span><span class="StringLiteral" style="color:#D93124">"NewQuarter"</span>, <span class="Keyword" style="color:#0070FF">IF</span><span class="Parenthesis" style="color:#969696"> (</span> [QuarterSubtotal], <span class="StringLiteral" style="color:#D93124">"TOTAL"</span>, [Quarter] <span class="Parenthesis" style="color:#969696">)</span><br><span class="Parenthesis" style="color:#969696">)</span><br>


and then you can build the graph:

 

JugglerJAF

Active Member
Joined
Feb 17, 2002
Messages
264
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.
 

Forum statistics

Threads
1,078,520
Messages
5,340,931
Members
399,399
Latest member
SravanaSandhya

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top