Forum Discussion

aleksrov's avatar
aleksrov
Frequent Visitor
7 years ago
Solved

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 an...
Need Help
  • aleksrov's avatar
    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.
     
aleksrov's avatar
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

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]))
sivab's avatar
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.