Count of Measure

[Anonymous]

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 average number of applications for a school.

 - Want to create a measure that counts the number of programs that receive applications below and above AppAverageSchool.

- Want to create a list and graph of such program

-  link of pbix file Count of Measure Issue

[MFelix · Accepted solution]

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]

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]

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]

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])

[Anonymous]

Hi @MFelix , one thing I forgot to mention is that I added tables for the count of the program above and below to the data model just for verification purpose and then a Date field to the context.  

The measure also breaks when I use the Date field in the slicer and even when I remove the new tables from the data model.

New Table = FILTER (
        SUMMARIZE (
            ALL ( 'Dim Programs'[Program Name] );
            'Dim Programs'[Program Name];
            "ApplicationCount"; [Applications]
        );
        [ApplicationCount] > [AppAverageSchool] )
    );
    [ApplicationCount])