Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Count of Measure

Hi,  Measure Applications counts the number of applications, ProgPerSchool measures distinct program in a school, AppPerSchool measures applications for a school, and AppAverageSchool calculate an a...
Need Help
Tutorial Requests
  • MFelix's avatar
    MFelix
    7 years ago

    Hi Anonymous ,

     

    Try to add the following two measures:

     

    Aplications Below = 
COUNTX (
    FILTER (
        SUMMARIZE (
            ALL ( 'Dim Programs'[Program Name] );
            'Dim Programs'[Program Name];
            "ApplicationCount"; [Applications]
        );
        [ApplicationCount] < [AppAverageSchool]
    );
    [ApplicationCount]
)

Aplications Above= 
COUNTX (
    FILTER (
        SUMMARIZE (
            ALL ( 'Dim Programs'[Program Name] );
            'Dim Programs'[Program Name];
            "ApplicationCount"; [Applications]
        );
        [ApplicationCount] > [AppAverageSchool]
    );
    [ApplicationCount]
)

    Regards,

    MFelix

MFelix's avatar
MFelix
Super User
7 years ago

Hi Anonymous ,

 

Try to add the following two measures:

 

Aplications Below = 
COUNTX (
    FILTER (
        SUMMARIZE (
            ALL ( 'Dim Programs'[Program Name] );
            'Dim Programs'[Program Name];
            "ApplicationCount"; [Applications]
        );
        [ApplicationCount] < [AppAverageSchool]
    );
    [ApplicationCount]
)

Aplications Above= 
COUNTX (
    FILTER (
        SUMMARIZE (
            ALL ( 'Dim Programs'[Program Name] );
            'Dim Programs'[Program Name];
            "ApplicationCount"; [Applications]
        );
        [ApplicationCount] > [AppAverageSchool]
    );
    [ApplicationCount]
)

Regards,

MFelix

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi MFelix , Thanks for the help.

     

    Can you please guide me on how to eliminate programs that have zero applications from the distinct count and measure.

     

    • MFelix's avatar
      MFelix
      Super User
      7 years ago

      Hi Anonymous ,

       

      You need to add another parameter on your filter try the following:

       

      Aplications Above= 
COUNTX (
    FILTER (
        SUMMARIZE (
            ALL ( 'Dim Programs'[Program Name] );
            'Dim Programs'[Program Name];
            "ApplicationCount"; [Applications]
        );
        [ApplicationCount] > [AppAverageSchool]  || [ApplicationCount] <> BLANK()
    );
    [ApplicationCount]
)

      Regards,

      MFelix

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi MFelix ,

         

        The measures work fine when I select a school and / or college for the count of programs but it breaks when I add a Date field to the context. Your guidance is highly appreciated.

         

        Applications =   CALCULATE ( DISTINCTCOUNT(Transactions[Transaction ID]) )

ProgPerSchool =    CALCULATE (  
                          DISTINCTCOUNT ( 'Dim Programs'[Program Code] ),
                          ALLEXCEPT('Dim Programs', Dim Programs'[School Name]),'Dim Programs'[Transaction_Count]>0  )

AppPerSchool =  CALCULATE ( [Applications], ALLEXCEPT ('Dim Programs','Dim Programs'[School Name]))

AppAverageSchl = DIVIDE([AppPerSchool],[ProgPerSchool],99999)

        Aplications Above= 
COUNTX (
    FILTER (
        SUMMARIZE (
            ALL ( 'Dim Programs'[Program Name] );
            'Dim Programs'[Program Name];
            "ApplicationCount"; [Applications]
        );
        [ApplicationCount] > [AppAverageSchool] )
    );
    [ApplicationCount])