Add yearly total to chart split by quarters

[JugglerJAF]

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]

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]

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.