manage 12 month column and year column into Power Bi desktop application

[jadhav_vilas84]

Dear Sir, 

Please help , I have data sheet as below . How can i manage into power Bi and shows month wise and year wise product sales 

Territory_Code	Region_Code	Zone_Code	Product	Bussiness	CFY_Apr_AnnualPlan_Qty	CFY_Apr_Expected_Qty	CFY_Apr_Actual_Sales_Qty	CFY_Apr_AnnualPlan_Value	CFY_Apr_Expected_Value	CFY_Apr_Product_sales	CFY_Apr_Product_Return	CFY_Apr_Product_Bill_Price	LFY_Apr_AnnualPlan_Qty	LFY_Apr_Expected_Qty	LFY_Apr_Actual_Sales_Qty	LFY_Apr_AnnualPlan_Value	LFY_Apr_Expected_Value	LFY_Apr_Product_sales	LFY_Apr_Product_Return	LFY_Apr_Product_Bill_Price
4	10030	30007	Tomato	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
4	10030	30007	brinjal	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
4	10030	30007	ALLQUIT	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
4	10030	30007	carrot	1	20	0	15	50860	0	49500	0	49500	20	0	15	50860	0	49500	0	49500
4	10030	30007	Bajra	1	0	0	10	0	0	4400	0	4400	0	0	10	0	0	4400	0	4400

[v-sihou-msft]

@jadhav_vilas84

Your requirement is not clear to me. It seems that you want the monthly sales and yearly sales. But I only find the product total sales of CFY and LFY in your data sheet.

Could you please describe it more clearly with expected result will be preferred.

Regards,

[jadhav_vilas84]

Dear Sir, 

I have Product sales and product returns data for Current Year with month like CFY_Apr_Product_sales  and last year with month  like (LFY_Apr_Product_sales ) , so I have total  24 columns for Product_sales , and it is based on folowing columns

Territory_Code

Region_Code

Zone_Code

Product

How can I manage this data in Power BI desktop application , Please guide. I want to  filter it using power Query like show top 10 territories product sales in may 2016 or more way.

Please guide for this.

thanks

vilas  jadhav

[v-sihou-msft · Accepted solution]

@jadhav_vilas84

In this scenario, you need to unpivot the CFY/LFY_Month_*** columns and add a separated Date column. Then add a column to store the rank number of product sales in each month.

Please refer to following steps.

1. In Query Editor, select all the CFY/LFY_Month_*** columns and click “Unpivot Columns”.
   
2. Select the ‘Attribute’ column above and click “Split Column by Delimiter” with custom delimiter ‘_’. Repeat once for the ‘Attribute.2’ column.
   
3. Select the ‘Attribute.1’ column and replace CFY with 2016 and LFY with 2015.
4. Select two columns of ‘Attribute.1’ and ‘Attribute.2.1’ and click “Merge Columns” with separator ‘Space’. Change this merged column type to “Date”.
   
5. Change the name of ‘Attribute.2.2’ to ‘Type’. Close and apply Query Editor.
   
6. Add a column to store the rank number of product sales in each month with below formula.
   

   Rank_In_Type = 
   RANKX (
       FILTER (
           Table1,
           EARLIER ( Table1[Type] ) = Table1[Type]
               && EARLIER ( Table1[Date] ) = Table1[Date]
       ),
       Table1[Value]
   )
   

7. Drag slicers and Clustered column chart into your canvas.
   

Regards,

[jadhav_vilas84]

Dear sir, 

My issue is resolve ,  thanks very much .

 I m not able to use rankx for serching top 10 products for sale. using power Query on dashboard , I m not able to search top 10 products whoes sales is heigh. Can you Please help me. 

thanks 

vilas jadhav