Forum Discussion

abukapsoun's avatar
abukapsoun
Post Patron
5 years ago
Solved

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

 

Need Help
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

    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

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

    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.

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

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