Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello all,
I need an idea 🙂
I have an Star Schema Model with one fact table and two dimensions
1. Dim Customer
2. Dim Product
The fact table sum the sales Amount, the fact table are connected to the dimensions.
My goal is to create a Matrix, that shows the TOP10 Customer by the sales amount, and the TOP 5 Products which bought by the customers.
A native TOPN Filter in Power BI Desktop doesn't work, because I can only use one TOP Filter.
For each Dimension a RANKX doest also not work, because I will get the RANKING fromthe customer and can with a less then filter the top 10, but the filter for the product will also not work, because the Product RANK doest not know which customers are selected.
Any ideas?
Thanks,
Philipp
Solved! Go to Solution.
@Anonymous
I guess there're different ways you could go about this. You could use the two below measures for instance, they may vary based on the requirement:
TOP10Cust Sales = VAR TOP10Cust = CALCULATETABLE( TOPN( 10, VALUES( Customer[CustomerKey] ), CALCULATE( SUMX( Sales, Sales[Quantity] * Sales[Unit Price] ) ), DESC ), ALLSELECTED( Customer ) ) RETURN CALCULATE( SUMX( Sales, Sales[Unit Price] * Sales[Quantity] ), KEEPFILTERS( TOP10Cust ) )
TOP 5 Prods of Top 10 Cust = VAR TOP10Cust = CALCULATETABLE( TOPN( 10, VALUES( Customer[CustomerKey] ), CALCULATE( SUMX( Sales, Sales[Quantity] * Sales[Unit Price] ) ), DESC ), ALLSELECTED( Customer ) ) RETURN IF( HASONEVALUE( Customer[CustomerKey] ), IF( VALUES( Customer[CustomerKey] ) in TOP10Cust, CONCATENATEX( TOPN( 5, VALUES( 'Product'[ProductKey] ), CALCULATE( SUMX( Sales, Sales[Quantity] * Sales[Unit Price] ) ), DESC ), 'Product'[ProductKey], ", " ) ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
@Anonymous
I guess there're different ways you could go about this. You could use the two below measures for instance, they may vary based on the requirement:
TOP10Cust Sales = VAR TOP10Cust = CALCULATETABLE( TOPN( 10, VALUES( Customer[CustomerKey] ), CALCULATE( SUMX( Sales, Sales[Quantity] * Sales[Unit Price] ) ), DESC ), ALLSELECTED( Customer ) ) RETURN CALCULATE( SUMX( Sales, Sales[Unit Price] * Sales[Quantity] ), KEEPFILTERS( TOP10Cust ) )
TOP 5 Prods of Top 10 Cust = VAR TOP10Cust = CALCULATETABLE( TOPN( 10, VALUES( Customer[CustomerKey] ), CALCULATE( SUMX( Sales, Sales[Quantity] * Sales[Unit Price] ) ), DESC ), ALLSELECTED( Customer ) ) RETURN IF( HASONEVALUE( Customer[CustomerKey] ), IF( VALUES( Customer[CustomerKey] ) in TOP10Cust, CONCATENATEX( TOPN( 5, VALUES( 'Product'[ProductKey] ), CALCULATE( SUMX( Sales, Sales[Quantity] * Sales[Unit Price] ) ), DESC ), 'Product'[ProductKey], ", " ) ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |