DAX, Get max value from measure value for each category

[adeeln]

Hi Everyone!

I'm having issue to solve one problem.
[Avg Customer Value] is measure in fact table which is derived as = Sum(Sales[Sales_Amount]) / Count(Customers)

I've a tenant dimension table where I've two columns (tcode, SalesCategory, Brand Name)
I want to calculate max [Avg Customer Value] for each Sales Category.

I've tried a measure using AllExcept

avg cust value by category = calculate ([Avg Customer Value], AllExcept(Tenant, Tenant[Brand]))

but is giving wrong results.

I want a maximum value of [Avg Customer Value] should be repeated with each category.

For example:

For Beauty Sales Category there is max [Avg Customer Value] = 984.56

so this value should be repeated with each beauty category.

Please help thanks.

[az38]

@adeeln hi

Not sure I understand you correcr but it seems like you need MAXX() function like

avg cust value by category = calculate (MAXX(Tenant, [Avg Customer Value]), AllExcept(Tenant, Tenant[Brand]))

[adeeln]

Hi @az38 ,

I've also tried this way, here are results. Now value gets high than max value.

Just FYI,

After reading these two blogs, I'm in doubt there should be using some rank or something else

https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/

https://community.powerbi.com/t5/Desktop/DAX-Find-MAX-value-of-a-Measure/td-p/295456

I'm near to solution but not sure how to get this value..

I've been used rank measure which is giving me correct rank by value.. Now I just want to get 1'st rank [Avg Cust Value].

avg cust value Max Brand rank = 
RANKX (
        CALCULATETABLE (
            VALUES ( 'Tenant'[Brand ] ),
            ALLSELECTED ( 'Tenant'[Brand ] )
        ),
        [Avg Customer Value LC]
    )

Here are rank accurate result with rank value = 1

But not sure how can I repeat rank 1st AVG Customer value with each category.

[az38]

Hi @adeeln 

I see for Cafes MAXX() returns correct result. Are you sure in your [Avg Customer Value] measure?

RANKX() opton is worse for your case from my opinion. Anyway, check this to calculate ran by categories and subcategories  https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns

[adeeln]

@az38 

Yes, Cafes is correct, but mostly data is wrong. I don't know why it's happening.
and [Avg Customer Value] is a measure which is equal  

Avg Customer Value = SUM (FACT[Sales Amount_Column]) / DISTINCTCOUNT(FACT[CustomersKey_Column])

Sorry, the only thing that I'm not getting how can I use rank to repeat the values for each category.

[az38]

@adeeln 

i see new potential issue

DISTINCTCOUNT(FACT[CustomersKey_Column])

can give you very unexpected result as one CustomersKey could exists in many Brands and categories

try also a measure like 

Avg Customer Value = SUM (FACT[Sales Amount_Column]) / CALCULATE(DISTINCTCOUNT(FACT[CustomersKey_Column]), AllExcept(Tenant, Tenant[Brand]))

 RANKX by categories for measure will look like

Rank all rows as Column (Sub Cat) = 
    RANKX(
        FILTER(
                'Table',
                'Table'[Category]=SELECTEDVALUE('Table'[Category])
                ),
        AVERAGE('Table'[My Value])
        )

[adeeln]

@az38 
I've used this DAX as well 

Avg Customer Value = SUM (FACT[Sales Amount_Column]) / CALCULATE(DISTINCTCOUNT(FACT[CustomersKey_Column]), AllExcept(Tenant, Tenant[Brand]))

The same thing is happening.

Let me clear you one thing. I'm working on Live SSAS connection it may due to that?

[az38]

@adeeln 

I don't think the problem is in SSAS

I would recommend to investigate your origin data and make sure about correct calculayion your distinctcount

[adeeln · Accepted solution]

@az38 

Got solution :-D.

The solution was hidden in RankX that I was shared above. Just change RankX with MAXx. 

Max Value by Each Category =
MAXX (
        CALCULATETABLE (
            VALUES ( 'Tenant'[Brand] ),
            ALLSELECTED ( 'Tenant'[Brand] )
        ),
        [Avg Customer Value LC]
    )

 
So the issue was not in Customer Distinct Count.

Thanks a lot for your help for your time, really appreciate.