cancel
Showing results for
Did you mean:
Highlighted Helper V

## Calculation support

Hi Gents,

Need your support with the following:

I have this table

 Type P01 P.. P12 CountryX Sales Coffee CountryX Sales Margin Coffee CountryX Sales Tea CountryX Sales Margin Tea

I Want to do the following:

Calculate Q1 Sales = P01+P02+P03

Calculate Q1 Sales Margin = P01+P02+P03

.

same until Q4

I thought of Unpivotting P01-P12 but then what?

I want to have a stacked graph where I can display 2 bars, one for sales and one for sales margin next to each others while the x-axis is the quarter.

And then create another stacked column to display the sales and sales margin while the X axis is the Type

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Solution Sage

## Re: Calculation support

Hi, @abukapsoun , how about calculating number of quarter after unpivoting P01-P12. The sales data then is tranformed to a one-dimensional table. A pbix file is attached for your reference. 2 REPLIES 2
Highlighted Solution Sage

## Re: Calculation support

Hi, @abukapsoun , how about calculating number of quarter after unpivoting P01-P12. The sales data then is tranformed to a one-dimensional table. A pbix file is attached for your reference. Highlighted Super User IX

## Re: Calculation support

@abukapsoun , First of unpivot the table. Then Create Period table With Period and Qtr , if you have the year you can add that. Or Create a year period table. Create period rank if needed qtr rank .

new column in period table

Qtr = Switch (true(),

[period] in {"P01","P02","P03"}, "1",

[period] in {"P04","P05","P06"}, "2"

}

Here Date is your period table

Month Rank = RANKX(all('Date'),'Date'[Period],,ASC,Dense)  // period or year -period
This Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))
Last year Month= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=(max('Date'[Month Rank]) -12)))

Qtr or Qtr Rank

Qtr Rank = RANKX(all('Date'),'Date'[Qtr],,ASC,Dense)  //on Qtr or Year - Qtr
This Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))

Proud to be a Super User!

Announcements #### Power Platform Community Conference

Check out the on demand sessions that are available now! #### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event. #### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021 #### Get Ready for Power BI Dev Camp 