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,077,850
Messages
5,336,740
Members
399,100
Latest member
darcob

Some videos you may like

This Week's Hot Topics

Top