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(
    KEEPFILTERS(VALUES('Query1'[Dc_nbr])),
    CALCULATE(SUM('Query1'[Damage Cases]))
)
 
Dc Count x Damage Cases average per Dc_nbr =
[Dc Count] * [Damage Cases average per Dc_nbr]
 
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
    )
)

4 Replies

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