Monthly Transactions analysis

[Sakhile]

Hi Guys,

May you please assist me with this analysis I have been struggling with for weeks now. This analysis has 2 parts

I have monthly transaction data that I am trying to workout the following  on alteryx to recreate what's in the pivot table in PIVOT TAB:

Part 1:

1. I need to workout the Monthly average of transactions per Branch Centre (See Pivot table column AH) 
2. I need to Multiply this Monthly of transactions per Branch Centre average by 1.2 /120% (See Pivot table column AI) 
3. I need to calculate the MIN and MAX no of Monthly of transactions per Branch Centre (See Pivot table column AJ and AK) 
4. I need to Count how many days had transactions exceeded the Average and Average by 1.2 per Branch Centre. (See Pivot table column AL and AM) 

Part 2

 Daily Average Per Branch Centre and Monthly Average of Grand total transactions for the month :

1. See ROW 154 where the Daily average has been calculated.
2. See ROW 155 and 156 where the Grand total transactions for the Month has been average and Average Multiplied by 1.2

Also If you could please try to group by or retain the Trans_Date column so that I can use this column to create a powerBI column and line graph with Day as X axis, Averages on Y axis and sum Quantity of transactions per day as columns.

Attached is the sample data and pivot table I am trying to recreate. 

[DataNath]

Hey @Sakhile, how does this look? Any issues let me know.

[Sakhile]

@DataNath 

Thanks for getting the figures. 

May you please format the  below figures to appear as Columns so that I can use them in Power BI Graph:

Monthly Average of Grand total transactions
Monthly Average of Grand total transactions*1.2

Daily Average All Centres

I also need the Trans_Date column to use as X axis in the graph

[DataNath · Accepted solution]

Hey @Sakhile, do you mean like this? I've assumed you want the average/average*1.2 as a constant or reference line so it runs throughout. I've split this off into a separate container for your PowerBI output. If not, if you can give me the exact format of the expected outcome then that'd be great. Amended workflow attached:

On a side note: Ditch Power BI and use Tableau! :D

[Sakhile]

Yes ! You are a Genius !!!!!!!!!!!!!