Need Following output in PowerBI

[Rajiv1237]

1: Raw Data

2: Output needed in PowerBI

3: Wrong Output Achieved in PowerBI

[v-shex-msft · Accepted solution]

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

[v-shex-msft · Accepted solution]

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

[Rajiv1237 · Accepted solution]

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
 END

[Rajiv1237]

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 PowerBI

Screenshot 2: Raw Data

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

[v-shex-msft]

Hi @Rajiv1237,

Can you share us a sample file to test?

Regards,
Xiaoxin Sheng

[Rajiv1237]

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.