Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I have two dimension tables and one fact table . I have Product Table (Dimension) , Customer Table ( Dimesnion) and Sales table (Fact). For Each Product, I would like display Top 3 Customer based on Sales.
Output is Table Chart:
Product A Customer1 $100
ProductA Customer2 $200
ProductA Cusotmer3 $300
Can you please help how to acheive using DAX in Power BI
Thanks,
Abhiram
// Create a measure that for each
// customer will tell you if
// they are in the top 3 cust for
// a product or not. Then use the measure
// as a filter for a table visual that will
// show you the existing combinations
// of Product and Customer and will display
// the [Total Sales] measure. Filter the
// visual by [Filtering Measure] = 1.
[Filtering Measure] =
var __oneProductAndCustomerVisible =
HASONEVALUE( Customer[CustID] )
&& HASONEVALUE( Product[ProdID] )
var __result =
if(
__oneProductAndCustomerVisible,
var __top3Customers =
CALCULATE(
// Bear in mind that if there
// are ties with respect to
// [Total Sales], all the qualified
// customers will be returned, so
// there might be more than 3.
topn(3,
VALUES( Customer[CustId] ),
[Total Sales],
DESC
),
ALL( Customer )
)
var __currentCustInTop3 =
VALUES( Customer[CustID] )
in __top3Customers
return
1 * __currentCustInTop3
)
return
__result
Thanks daxer ! I'm gettng error in below part of code.
Funciton expects table expression for argument but string was used
var __currentCustInTop3 =
VALUES( Customer[CustID] )
in __top3Customers
Thanks,
Abhiram
Hi @abhiram342 ,
You can use this measure
Top 3 =
SWITCH(
TRUE(),
ISINSCOPE(Customer[Name]), RANKX(ALL('Customer'[Name]),[Total Sales]),
ISINSCOPE('Item'[Brand]), RANKX(ALL('Item'[Brand]), [Total Sales])
)
Create a Visual Filter where Top3 is less than 3.
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Thanks Harsh ! I have replaced with actual tables and the DAX code is runnig for more than 10 mins . Can you please suggest if we have any alternatives
Thanks,
Abhiram
@abhiram342 - You may find this helpful.
https://community.powerbi.com/t5/Quick-Measures-Gallery/To-Bleep-with-RANKX/m-p/1042520#M452
Otherwise, Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
@abhiram342 , refer this
https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...
Thanks Amit ! I referred to below blog and using measure simialr to this but da code take long time
>>Top 3 =
SWITCH(
TRUE(),
ISINSCOPE(Customer[Name]), RANKX(ALL('Customer'[Name]),[Total Sales]),
ISINSCOPE('Item'[Brand]), RANKX(ALL('Item'[Brand]), [Total Sales])
)
Thanks,
Abhiram
User | Count |
---|---|
47 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |