Help in Matrix Report

[rajibmahmud]

Hi,

I am trying to prepare a Matrix Report which is pulling data from 2 different Dataset/Table. Which are linked using Relationship tables for Measure & Year which are common in both the table and there is unique mapping table for this.

However the problem is in the table the value is getting pulled in 2 different value column in the table, which is not serving my purpose.

Screenshot in given below:

Also, is there anyway in system to view reports in below format? I can put either month or year in the column. 

2012

2013

Measure

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Secondary Sales

Volumn

My clients request is more complex actually, they want to see Quarterly value for 2 year, rest in month like below:

2012

2013

2014

Measure

Q1

Q2

Q3

Q4

Q1

Q2

Q3

Q4

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Secondary Sales

Volumn

[v-huizhn-msft]

Hi @rajibmahmud,

Have you resolved your issue? If you have, please mark the right or helpful reply as answer, so more people will benefit from it. If you still want to clear something up, please feel free to ask.

Best Regards,
Angelia

[Greg_Deckler · Accepted solution]

Create a disconnected table (not related to anything) of the measures that you want to display, Volume, Sec Sales, etc.

In my example my table is called "Measure 1" and I have a column in it called "Measure".

Create a measure, I called mine "MyMeasure" that has a formula:

MyMeasure = MAX('Measures 1'[Measure])

Create another measure, I called mine "MyValue" that has a formula:

MyValue = IF(HASONEVALUE('Measures 1'[Measure]),SWITCH([MyMeasure],"Volume",SUM([Volume]),"Sec Sales",SUM([Sec Sales]),BLANK()),BLANK())

Place "Measure 1 | Measure" into your rows.

Place Year, Quarter, Month into your columns.

Place MyValue into your values.

Use the double connected arrow drill down.

[rajibmahmud]

@Greg_Deckler

Thanks. 

The measures Mymeasure and Myvalue will be in the disconnected table?

Also, can I use this method for 3 or 4 table/datasource as well?

[Greg_Deckler]

Measures can be placed anywhere.

I'd have to see your data to be certain, but the technique will work for multiple tables, that shouldn't be an issue. 

[rajibmahmud]

@Greg_Deckler

I will try this in my dataset today dayend, but let me fully understand the concept first. As I am didnt understand the logic yet. So this measure, will check data in  all the tables and take the max value, right? And as I dont have value in Secondary Sales measure in Internal Penetration table, so it will pick up internal penetration value as max. However, there could be scenario where Secondary sales measure will not have any value in Int. Pen table but Internal penetration value is negative. What would happen on that scenario?

[Greg_Deckler]

The MAX is being applied to the measure name "Sec Sales", "Volume", etc. It is only there because Power BI Measures need to have an aggregation function of some sort and MAX works with text values. You could just as well use MIN. Based upon the value of the measure name, the SWITCH statement then returns the correct calculation for that measure.

Note, I am using capital M Measure to refer to Power BI Measures and little m measure to refer to the metrics that you want to show.