cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

Calculation support

Hi Gents,

 

Need your support with the following:

 

I have this table

  TypeP01P..P12
CountryXSalesCoffee   
CountryXSales MarginCoffee   
CountryXSalesTea   
CountryXSales MarginTea   

 

 

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
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.

Screenshot 2020-09-13 195934.png

View solution in original post

2 REPLIES 2
Highlighted
Solution Sage
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.

Screenshot 2020-09-13 195934.png

View solution in original post

Highlighted
Super User IX
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"

///keep on adding

}

 

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))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors