Skip to main content
cancel
Showing results for 
Search instead 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

Reply
adeeln
Post Patron
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.

 

Annotation.png

 

 

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.

1 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.

 

 

View solution in original post

8 REPLIES 8
az38
Community Champion
Community Champion

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

 


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

Hi @az38 ,

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

 

Annotation.png

 

 

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

Annotation.png

 

 

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

az38
Community Champion
Community Champion

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


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

@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
Community Champion
Community Champion

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

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

@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
Community Champion
Community Champion

@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


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

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

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.