Measure values in rows and columns in Matrix

[aleksrov]

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!                                                                                      

[Anonymous]

@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.

[v-chuncz-msft]

@aleksrov,

You may take a look at Drill down on column headers and Show values on rows for matrix.

[aleksrov · Accepted solution]

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.

[Greg_Deckler]

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]

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)

[aleksrov]

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]

@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.