Solved: Re: Need Help with DAX!!

Debsidrian · ‎09-18-2023

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]

File: VODC V1.1.pbix

parry2k · ‎09-18-2023

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post