cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Patron

## DAX, Get max value from measure value for each category

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.

1 ACCEPTED SOLUTION
Post Patron

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.

8 REPLIES 8
Community Champion

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]))``

do not hesitate to give a kudo to useful posts and mark solutions as solution
Post Patron

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.

Community Champion

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

do not hesitate to give a kudo to useful posts and mark solutions as solution
Post Patron

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.

Community Champion

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])
)``````

do not hesitate to give a kudo to useful posts and mark solutions as solution
Post Patron

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

Community Champion

I don't think the problem is in SSAS

do not hesitate to give a kudo to useful posts and mark solutions as solution
Post Patron

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.