Forum Discussion

Debsidrian's avatar
Debsidrian
Frequent Visitor
2 years ago
Solved

Need Help with DAX!!

I am not getting the correct calculation out of DAX.        Right number should be 107,568   Dc Count = DISTINCTCOUNT(Query1[Dc_nbr])   Damage Cases average per Dc_nbr = AVERAGEX( ...
Need Help
Tips and Tricks
  • parry2k's avatar
    parry2k
    2 years ago

    Debsidrian the issue is that 2988 is shown based on the top 5 dc_nbr based on the sum of damage cases whereas when you are multiplying the measure there is no TOP 5 and that's why you are getting a different number.

     

    Either you need to create a seperate measure as below for TOP 5 and then use that in the multiplication and then it will work:

     

    Damage Cases average per Dc_nbr test = 
CALCULATE (
    AVERAGEX (
        KEEPFILTERS ( VALUES ( 'Query1'[Dc_nbr] ) ),
        CALCULATE ( SUM ( 'Query1'[Damage Cases] ) )
    ), 
    TOPN ( 
        5,
        ALLSELECTED ( Query1[Dc_nbr] ),
        CALCULATE ( SUM ( Query1[Damage Cases] ) ),
        DESC
    )
)
parry2k's avatar
parry2k
Super User
2 years ago

Debsidrian the issue is that 2988 is shown based on the top 5 dc_nbr based on the sum of damage cases whereas when you are multiplying the measure there is no TOP 5 and that's why you are getting a different number.

 

Either you need to create a seperate measure as below for TOP 5 and then use that in the multiplication and then it will work:

 

Damage Cases average per Dc_nbr test = 
CALCULATE (
    AVERAGEX (
        KEEPFILTERS ( VALUES ( 'Query1'[Dc_nbr] ) ),
        CALCULATE ( SUM ( 'Query1'[Damage Cases] ) )
    ), 
    TOPN ( 
        5,
        ALLSELECTED ( Query1[Dc_nbr] ),
        CALCULATE ( SUM ( Query1[Damage Cases] ) ),
        DESC
    )
)
  • Debsidrian's avatar
    Debsidrian
    Frequent Visitor
    2 years ago

    Thank you!! that worked!