Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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.
Solved! Go to Solution.
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.
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.
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
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.
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])
)
@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?
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.
User | Count |
---|---|
84 | |
80 | |
70 | |
47 | |
43 |
User | Count |
---|---|
109 | |
54 | |
50 | |
40 | |
40 |