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