cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
aleksrov Frequent Visitor
Frequent Visitor

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!                                                                                      Безымянный.png

1 ACCEPTED SOLUTION

Accepted Solutions
aleksrov Frequent Visitor
Frequent Visitor

Re: Measure values in rows and columns in Matrix

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 

image.png

 

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.
image.png                                                                    
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.
 
5 REPLIES 5
Super User
Super User

Re: Measure values in rows and columns in Matrix

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


aleksrov Frequent Visitor
Frequent Visitor

Re: Measure values in rows and columns in Matrix

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)image.pngModel                                                     FactApplications

FactApplicationKeyApplicationKeyGeographyKeyCredit NumberHas Previous Success AppLoan SumCreatedAt
11706453320720NoNULL20190116
11706454320720NoNULL20190116
11706455293120NoNULL20190116

Fact Contract   

FactContractKeyContract IDApplicationKeyGeographyKeyAmountCredit NumberDisbursementDate
109965422526332072129120190113
110089922650832072300120190114
110107822668729312129120190114

ApplicationsAttribute

ApplicationKeyApplication TypeTermChannel ShortRepeat Sale
3207PDL30WEBNo
2931PDL30Mobile

No

Geography

GeographyKeyCountry
2ID

FactSalePlan

FactSalePlanKeyDateGeographyKeyApplicationKeyFirstLoanDisbursedCountApplications
48720190116232071851000
48820190116229311851000

 

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]))
aleksrov Frequent Visitor
Frequent Visitor

Re: Measure values in rows and columns in Matrix

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)

 

image.png

Community Support Team
Community Support Team

Re: Measure values in rows and columns in Matrix

@aleksrov,

 

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
aleksrov Frequent Visitor
Frequent Visitor

Re: Measure values in rows and columns in Matrix

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 

image.png

 

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.
image.png                                                                    
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.