Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Rajiv1237
Resolver I
Resolver I

Need Following output in PowerBI

1: Raw DataRaw Data

2: Output needed in PowerBIOutput needed in PowerBI

3: Wrong Output Achieved in PowerBIOutput Achieved in PowerBI

 

2 ACCEPTED SOLUTIONS
v-shex-msft
Community Support
Community Support

Hi @Rajiv1237,

 

You can refer to below steps to achieve your requirement.

 

1. Output needed in PowerBI:

Drag month to Rows filed, year to Columns fields, year and qty to Values fields.

 

2. Output Achieved in PowerBI:

Add a calculate to column to merge month and year.

 

Sample:

Date=CONCATENATE([Month]&".",[Year])

 

Use above calculate column as the Rows of the matrix, year to Columns field, QTY and measure to columns field.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Issue was with my PowerBI solution:

 

I have DateKey identity column and I have sorted my Month column with that DateKey.

 

After removing Month column sorting with DateKey column I got the desired output.

 

And for sorting Month column instead of identity column I used

For Month column value January DateKey Column Value =  1

 

Update Query:

Update [dbo].[YearQty]
SET DateKey = CASE
 WHEN [Month] = 'January' THEN 1
 WHEN [Month] = 'February' THEN 2
 WHEN [Month] = 'March' THEN 3
 WHEN [Month] = 'April' THEN 4
 WHEN [Month] = 'May' THEN 5
 WHEN [Month] = 'June' THEN 6
 WHEN [Month] = 'July' THEN 7
 WHEN [Month] = 'August' THEN 8
 WHEN [Month] = 'September' THEN 9
 WHEN [Month] = 'October' THEN 10
 WHEN [Month] = 'November' THEN 11
 WHEN [Month] = 'December' THEN 12
 ENDCorrect_Output.PNG

View solution in original post

5 REPLIES 5
v-shex-msft
Community Support
Community Support

Hi @Rajiv1237,

 

You can refer to below steps to achieve your requirement.

 

1. Output needed in PowerBI:

Drag month to Rows filed, year to Columns fields, year and qty to Values fields.

 

2. Output Achieved in PowerBI:

Add a calculate to column to merge month and year.

 

Sample:

Date=CONCATENATE([Month]&".",[Year])

 

Use above calculate column as the Rows of the matrix, year to Columns field, QTY and measure to columns field.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Issue was with my PowerBI solution:

 

I have DateKey identity column and I have sorted my Month column with that DateKey.

 

After removing Month column sorting with DateKey column I got the desired output.

 

And for sorting Month column instead of identity column I used

For Month column value January DateKey Column Value =  1

 

Update Query:

Update [dbo].[YearQty]
SET DateKey = CASE
 WHEN [Month] = 'January' THEN 1
 WHEN [Month] = 'February' THEN 2
 WHEN [Month] = 'March' THEN 3
 WHEN [Month] = 'April' THEN 4
 WHEN [Month] = 'May' THEN 5
 WHEN [Month] = 'June' THEN 6
 WHEN [Month] = 'July' THEN 7
 WHEN [Month] = 'August' THEN 8
 WHEN [Month] = 'September' THEN 9
 WHEN [Month] = 'October' THEN 10
 WHEN [Month] = 'November' THEN 11
 WHEN [Month] = 'December' THEN 12
 ENDCorrect_Output.PNG

Hello Xiaoxin Sheng,

 

I need help to get output shown in Screenshot 1: Need this output in PowerBI.

 

I already tried the steps mentioned by you:

Month column in Rows

Year column in Columns

Qty column in values

 

But I get output like shown in screenshot 3 😞 .

 

 

Screenshot1: Need this output in PowerBINeed this output in PowerBI

Screenshot 2: Raw DataRaw Data

Screenshot 3: Output got by putting month in Rows, Year in Columns and Qty in ValuesOutput got by putting month in Rows, Year in Columns and Qty in Values

Hi @Rajiv1237,

 

Can you share us a sample file to test?

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

I kept the file in one drive in a zip folder:

 

OneDrive: https://1drv.ms/u/s!At1dYCJDAOTVlEOMMIbrYNh1G0I-

 

Google Drive:

https://drive.google.com/drive/folders/0B4uLnf8d7YTqTk9iN2tYTU5jSW8?usp=sharing

 

If you are unable to access this file, Please share your mail id.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.