Using Table or Matrix

[Adriankoh]

Hi experts,

I am new to Power BI, I was trying to use a Matrix or table to show some tabular report I was expecting.

For example:

 For columns:

Customer No.

Customer Name

Jan Sales (base on my dataset date)

Feb Sales (base on my dataset date)

Mar Sales (base on my dataset date)

Apr Sales (base on my dataset date)

May Sales (base on my dataset date)

Jun Sales (base on my dataset date)

I tried pulling customer name to either row or column but it didn't work, when customer no. and name both exist on the row, the customer name became a second level hierarchy of customer no.

Would appreciate if someone can advise how I achieve what I wanted to. I believed of course i have to create a DAX to sum my sales amount, that one I can do it. So, is it I have to create 12 calculated columns for all the months in a year with my dataset date column?

Thanks in advance.

[miTutorials]

You can also change the layout of the Matrix to Tabular - check out the video below.

Power BI May 2024 Update: New Matrix Layouts, Line Enhancements & Manage Relationships | MiTutorials...

[Adriankoh]

Hi, thank you for your prompt reply. However, this video does not reply to my question.

[rajendraongole1 · Accepted solution]

Hi @Adriankoh -

you can add a matrix visual:

Monthly Sales =
VAR SelectedMonth = SELECTEDVALUE(DateTable[MonthNumber])
RETURN
CALCULATE(
SUM(Sales[SalesAmount]),
MONTH(Sales[Date]) = SelectedMonth
)

add this Monthly sales in Values using Matrix chart

Drag Customer No. and Customer Name to the Rows section.
Drag DateTable[MonthName] to the Columns section.
Drag Monthly Sales measure to the Values section.

Hope it helps

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Adriankoh]

Hi Sir,

Thank you for your suggestion. I tried that, and now my problem is the "Customer No." and "Customer Name" became a grouping, similar issue with what I have done previously, as shown.

A "Customer No." represents one "Customer Name", so I would like them to be in different columns showing in one line. Is it possible?

[Adriankoh]

The most I achieved in this way.

Is it possible for the month to show as three characters' month? Like "APR" , "MAY"? I use month name, then the sequence of the month sorted by month name and messed up the report.

[Anonymous]

Hi @Adriankoh ,

You can create a matrix visual with the same settings with the one in below screenshot:

Fields settings

Layout style as Tabular

Toggle off the option "+/- icons"

Best Regards

[Adriankoh]

Hi,

As I said, I know I can use Month Name and I have tried, however, the month sequence gone haywire and became ascending by month name, which is not what I want.

[Adriankoh]

Dear Sir,

Thank you very much, later I will try to follow and see, and will come back to you.