Expression in DAX

[Kavya123]

Hi Everyone,

We are converting report from Qlikview to Power BI. Please help me to write DAX expression for the same. I wrote the dax expression but I am not getting expected result.

Dax expression:

Measure 3 Quality & Innovation = CALCULATE(AVERAGE('PROFILERTABLENEWSUBMITTED Score'[NEWScore]), FILTER('PROFILERTABLENEWSUBMITTED Score', 'PROFILERTABLENEWSUBMITTED Score'[PDataPoint]=1209), FILTER('PROFILERTABLENEWSUBMITTED Score','PROFILERTABLENEWSUBMITTED Score'[Answer_Flag]=1))

Measure 4 Quality & Innovation = CALCULATE(AVERAGE('PROFILERTABLENEWSUBMITTED Score'[NEWScore]), FILTER('PROFILERTABLENEWSUBMITTED Score', 'PROFILERTABLENEWSUBMITTED Score'[PDataPoint]=1210), FILTER('PROFILERTABLENEWSUBMITTED Score','PROFILERTABLENEWSUBMITTED Score'[Answer_Flag]=1))

Measure 2 Quality & Innovation = FLOOR(CALCULATE(AVERAGE( 'PROFILERTABLENEWSUBMITTED Score'[NEWScore]) , 'PROFILERTABLENEWSUBMITTED Score'[PDataPoint]=1210, 'PROFILERTABLENEWSUBMITTED Score'[Answer_Flag]=1,ALL('PROFILERTABLENEWSUBMITTED Score'[POrganization])) ,1)

CALCULATE( COUNTROWS (

FILTER (

VALUES( 'PROFILERTABLENEWSUBMITTED Score'[POrganization]),

if ( and ([Measure 2 Quality & Innovation]>[Measure 4 Quality & Innovation], [Measure 3 Quality & Innovation]<[Measure 4 Quality & Innovation]), 1,0) )) , FILTER( 'PROFILERTABLENEWSUBMITTED Score', 'PROFILERTABLENEWSUBMITTED Score'[PDataPoint]=1209 || 'PROFILERTABLENEWSUBMITTED Score'[PDataPoint]=1210))

We are calculating the Count of POrganization WHERE the average score for PDataPoint 1210 is less than the total average (rounded down to nearest integer) AND the average score for PDataPoint 1210 is larger than the average score for PDataPoint 1209. 

[v-yiruan-msft]

Hi @Kavya123 ,

Did you get the expected result base on my provided measure formulas? If yes, could you please mark it as Answered? It will help the others find the solution easily if they face the same problem with you. Thank you.

Best Regards

[v-yiruan-msft]

Hi @Kavya123 ,

You can create the measeures as below:

Avg score of 1209 =
AVERAGEX (
    FILTER (
        ALLSELECTED ( 'PROFILERTABLENEWSUBMITTED Score' ),
        'PROFILERTABLENEWSUBMITTED Score'[PDataPoint] = 1209
            && 'PROFILERTABLENEWSUBMITTED Score'[Answer_Flag] = 1
    ),
    'PROFILERTABLENEWSUBMITTED Score'[NEWScore]
)

Avg score of 1210 =
AVERAGEX (
    FILTER (
        ALLSELECTED ( 'PROFILERTABLENEWSUBMITTED Score' ),
        'PROFILERTABLENEWSUBMITTED Score'[PDataPoint] = 1210
            && 'PROFILERTABLENEWSUBMITTED Score'[Answer_Flag] = 1
    ),
    'PROFILERTABLENEWSUBMITTED Score'[NEWScore]
)

Avg score of Total =
FLOOR (
    AVERAGEX (
        FILTER (
            ALLSELECTED ( 'PROFILERTABLENEWSUBMITTED Score' ),
            'PROFILERTABLENEWSUBMITTED Score'[Answer_Flag] = 1
        ),
        'PROFILERTABLENEWSUBMITTED Score'[NEWScore]
    ),
    1
)

Measure =
CALCULATE (
    DISTINCTCOUNT ( 'PROFILERTABLENEWSUBMITTED Score'[POrganization] ),
    FILTER (
        ALLSELECTED ( 'PROFILERTABLENEWSUBMITTED Score' ),
        [Avg score of 1210] < [Avg score of total]
            && [Avg score of 1210] > [Avg score of 1209]
    )
) + 0

If the above one is not working, please provide some sample data (exclude sensitive data) and your expected result with sample or screenshots. Thank you.

Best Regards

[lbendlin]

Please provide sample data in usable format (not as a picture) and show the expected outcome.