Measure values in rows and columns in Matrix

Hello everyone! I have a problem, I'm working with report and need make it like it was in Excell. There are measures like one, two, three, etc, that's rows, and there are mesures like goal, actual and average, that's columns. I made measures, but I have no idea how to do matrix like in Excell, I was wondering if this is possible, Thanks in advance!

Need Help

aleksrov
7 years ago
Thank you for answer, but it's not that. I found solution, it's not perfect, but business get what it want. So, I add two columns to FactApplications, Actual (TypeOne) and Average (TypeThree) (both hide of course), in first always 1, in second 3, then i created table TypeMeasure, like that
ID_type - 1,2,3 and Type - Actual, Goal, Average, then I created relationship

then i craeted measure
Apps Count =
CALCULATE(COUNT('FactApplications'[FactApplicationKey]);
FILTER (
'FactApplications';
(
(
'FactApplications'[Has Previous Success App] = "No"
&& RELATED ( 'ApplicationAttribute'[Channel Short] ) = "Mobile"
)
|| ( RELATED ( 'ApplicationAttribute'[Channel Short] ) <> "Mobile" )
)
&& RELATED ( 'ApplicationAttribute'[Repeat Sale] ) = "No"
);USERELATIONSHIP(FactApplications[TypeOne];TypeMeasure[ID_Type])
)
+CALCULATE(SUM(FactSalePlan[CountApplications]);USERELATIONSHIP(FactSalePlan[TypeTwo];TypeMeasure[ID_Type]))
+CALCULATE(Divide(CALCULATE(COUNT('FactApplications'[FactApplicationKey]);
FILTER (
'FactApplications';
(
(
'FactApplications'[Has Previous Success App] = "No"
&& RELATED ( 'ApplicationAttribute'[Channel Short] ) = "Mobile"
)
|| ( RELATED ( 'ApplicationAttribute'[Channel Short] ) <> "Mobile" )
)
&& RELATED ( 'ApplicationAttribute'[Repeat Sale] ) = "No"
)
); DISTINCTCOUNT(FactApplications[CreatedAt]); 0); ALL(ReportDate[Date]);USERELATIONSHIP(FactApplications[TypeThree];TypeMeasure[ID_Type]))
That's only test, of course I'll create variables.
So, I got what i want.

I know which problem with this measure can appear, but this was done only for one and only one report, because business want like it was in excell (in excell it fills in by hand). If someone want to see only actual or only average for example, I created another measure without relationship, ome measure for actual, one for goal and etc.

7 Replies

v-chuncz-msft
Community Support
7 years ago
aleksrov,

You may take a look at Drill down on column headers and Show values on rows for matrix.
- aleksrov
  Frequent Visitor
  7 years ago
  Thank you for answer, but it's not that. I found solution, it's not perfect, but business get what it want. So, I add two columns to FactApplications, Actual (TypeOne) and Average (TypeThree) (both hide of course), in first always 1, in second 3, then i created table TypeMeasure, like that
  ID_type - 1,2,3 and Type - Actual, Goal, Average, then I created relationship
  
  then i craeted measure
  Apps Count =
  CALCULATE(COUNT('FactApplications'[FactApplicationKey]);
  FILTER (
  'FactApplications';
  (
  (
  'FactApplications'[Has Previous Success App] = "No"
  && RELATED ( 'ApplicationAttribute'[Channel Short] ) = "Mobile"
  )
  || ( RELATED ( 'ApplicationAttribute'[Channel Short] ) <> "Mobile" )
  )
  && RELATED ( 'ApplicationAttribute'[Repeat Sale] ) = "No"
  );USERELATIONSHIP(FactApplications[TypeOne];TypeMeasure[ID_Type])
  )
  +CALCULATE(SUM(FactSalePlan[CountApplications]);USERELATIONSHIP(FactSalePlan[TypeTwo];TypeMeasure[ID_Type]))
  +CALCULATE(Divide(CALCULATE(COUNT('FactApplications'[FactApplicationKey]);
  FILTER (
  'FactApplications';
  (
  (
  'FactApplications'[Has Previous Success App] = "No"
  && RELATED ( 'ApplicationAttribute'[Channel Short] ) = "Mobile"
  )
  || ( RELATED ( 'ApplicationAttribute'[Channel Short] ) <> "Mobile" )
  )
  && RELATED ( 'ApplicationAttribute'[Repeat Sale] ) = "No"
  )
  ); DISTINCTCOUNT(FactApplications[CreatedAt]); 0); ALL(ReportDate[Date]);USERELATIONSHIP(FactApplications[TypeThree];TypeMeasure[ID_Type]))
  That's only test, of course I'll create variables.
  So, I got what i want.
  
  I know which problem with this measure can appear, but this was done only for one and only one report, because business want like it was in excell (in excell it fills in by hand). If someone want to see only actual or only average for example, I created another measure without relationship, ome measure for actual, one for goal and etc.
Anonymous
Not applicable
6 years ago
aleksrov In format section of Matrix , if you go to values, Keep "show on rows" on. and search stepped layout ,make that off. I got the solution by doing this. Please check in your case.
Greg_Deckler
Community Champion
7 years ago
Any posibility you can supply some sample data? You have what you want it to look like in the end but sample data and your measure calculations/formulas would assist greatly. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
- aleksrov
  Frequent Visitor
  7 years ago
  Thank you for answer! Yes, I supplied a small piece of data, sorry I didn't do it before. And I forgot to tell, powerbi use live connection to SSAS Tabular 2017. In sample only mains columns and of course that report will be main sale report with more than 30 measures, but the logic the same, in Rows Name of indicator, in columns channel and goal, actual and average of this indicator. In sample data there are three measure Apps Count, Apps Count Goal and Apps Count Avarage and three measure First Loan Count, First Loan Goal and First Loan Avarage, these must be columns with channel above it (like in Excell), and name this indicator in row (Apps Count and First Loan)Model FactApplications
  FactApplicationKey ApplicationKey GeographyKey Credit Number Has Previous Success App Loan Sum CreatedAt
  11706453 3207 2 0 No NULL 20190116
  11706454 3207 2 0 No NULL 20190116
  11706455 2931 2 0 No NULL 20190116
  Fact Contract
  FactContractKey Contract ID ApplicationKey GeographyKey Amount Credit Number DisbursementDate
  1099654 225263 3207 2 129 1 20190113
  1100899 226508 3207 2 300 1 20190114
  1101078 226687 2931 2 129 1 20190114
  ApplicationsAttribute
  ApplicationKey Application Type Term Channel Short Repeat Sale
  3207 PDL 30 WEB No
  2931 PDL 30 Mobile
  No
  Geography
  GeographyKey Country
  2 ID
  FactSalePlan
  FactSalePlanKey Date GeographyKey ApplicationKey FirstLoanDisbursed CountApplications
  487 20190116 2 3207 185 1000
  488 20190116 2 2931 185 1000
  
  Measures
  Apps Count =
  COUNTX (
  FILTER (
  'FactApplications';
  (
  (
  'FactApplications'[Has Previous Success App] = "No"
  && RELATED ( 'ApplicationAttribute'[Channel Short] ) = "Mobile"
  )
  || ( RELATED ( 'ApplicationAttribute'[Channel Short] ) <> "Mobile" )
  )
  && RELATED ( 'ApplicationAttribute'[Repeat Sale] ) = "No"
  );
  'FactApplications'[FactApplicationKey]
  )
  Apps Count Goal = SUM(FactSalePlan[CountApplications])
  
  Apps Count Avarage = Divide([Apps Count]; DISTINCTCOUNT(FactApplications[CreatedAt]))
  
  First Loan Disbursed =
  CALCULATE ( COUNT ( FactContract[FactContractKey] ); FactContract[Credit Number] = 1 )
  
  First Loan Disbursed Goal = Sum(FactSalePlan[FirstLoanDisbursed])
  
  First Loan Disbursed Avarage = Divide([First Loan Disbursed]; DISTINCTCOUNT(FactContract[DisbursementDate]))
  - sivab
    Frequent Visitor
    5 years ago
    aleksrov My expected out put is similar to your's in Matrix visual. I can able to do it in row level but unable to display the Growth%. Could you share your solution how did you solve this.
- aleksrov
  Frequent Visitor
  7 years ago
  Sorry, I forgot about one more table, ReportDate.
  It has relationship like
  ReportDate(DateKey) 1 --> * FactApplications(CreatedAt)
  ReportDate(DateKey) 1 --> * FactContrac(DisbursementDate)
  ReportDate(DateKey) 1 --> * FactSalePlan(Date)

Forum Discussion

Measure values in rows and columns in Matrix

7 Replies

Related content

Blank columns when doing Measure Name Measure Value approach

Total Value Column Measure

Measure two columns

SUMX(VALUES(column), [Measure]) returns parent-level value, while ADDCOLUMNS/SUMMARIZECOLUMNS works

Splitting Column values

FactApplicationKey	ApplicationKey	GeographyKey	Credit Number	Has Previous Success App	Loan Sum	CreatedAt
11706453	3207	2	0	No	NULL	20190116
11706454	3207	2	0	No	NULL	20190116
11706455	2931	2	0	No	NULL	20190116

FactContractKey	Contract ID	ApplicationKey	GeographyKey	Amount	Credit Number	DisbursementDate
1099654	225263	3207	2	129	1	20190113
1100899	226508	3207	2	300	1	20190114
1101078	226687	2931	2	129	1	20190114

ApplicationKey	Application Type	Term	Channel Short	Repeat Sale
3207	PDL	30	WEB	No
2931	PDL	30	Mobile	No

FactSalePlanKey	Date	GeographyKey	ApplicationKey	FirstLoanDisbursed	CountApplications
487	20190116	2	3207	185	1000
488	20190116	2	2931	185	1000

GeographyKey	Country
2	ID