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.
Good morning,
I'm creating a report that would focus on the top 10 Accounts in the selected period, based on the quantity sold of a specifc product.
In the Sales table there are 5 different Product Types, and each one of these types has several ProductName.
Example:
ProductType | ProductName |
GPU | RTX 3080 |
GPU | RTX 3070 |
GPU | RTX 3060 |
CPU | Ryzen 7 3700x |
CPU | Ryzen 9 3900x |
CPU | Ryzen 5 2600x |
In the report I'm creating I want to show the top 10 Accounts per GPU sold.The measure looks like:
m_GPU_Qty = Calculate(SUM('Sales(Product Level)'[Quantity]), 'Sales(Product Level)'[ProductType]="GPU").
I want to create a card box that would show the top 1 ProductName, among the top 10 Accounts filtered by quantity of GPU sold.
Unfortunately I found out that it is not possible to include two Top(N) filters in the view.
Thanks for your attention,
Alessio B.
Solved! Go to Solution.
Hi @Alex-PBIComm ,
So far, I have completed your first request, and I have encountered some difficulties with your second request. Please wait patiently until I come back next week during work hours. I will continue to study.
The measure is
top 5 Accounts per GPU sold =
VAR _RANK =
RANKX (
ALL ( 'Sales(Product Level)' ),
CALCULATE (
SUM ( 'Sales(Product Level)'[Quantity] ),
FILTER (
ALLEXCEPT ( 'Sales(Product Level)', 'Sales(Product Level)'[AccountID] ),
[ProductType] = "GPU"
)
),
,
DESC,
DENSE
)
RETURN
IF ( _RANK <= 5, _RANK )
Because there are only 7 account IDs in my sample data, I returned the top five account IDs.
I apologize again for not solving your problem in time.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Alex-PBIComm ,
After my test, creating a measure to get the top 1 product name does not work.
I finally achieved it by creating two tables. Because it is written in dax, if your data is updated, the result will also be updated.
TOP5ACCOUNT ID =
FILTER (
SUMMARIZE (
FILTER ( 'Sales(Product Level)', [ProductType] = "GPU" ),
[ProductName],
[Quantity],
"top5",
RANKX (
FILTER ( ALL ( 'Sales(Product Level)' ), [ProductType] = "GPU" ),
CALCULATE (
SUM ( 'Sales(Product Level)'[Quantity] ),
FILTER (
ALLEXCEPT ( 'Sales(Product Level)', 'Sales(Product Level)'[AccountID] ),
[ProductType] = "GPU"
)
),
,
DESC,
DENSE
)
),
[top5] <= 5
)
TOP 1 ProductName =
FILTER (
SUMMARIZE (
'TOP5ACCOUNT ID',
[ProductName],
[Quantity],
"top1",
RANKX (
ALL ( 'TOP5ACCOUNT ID' ),
CALCULATE (
SUM ( 'TOP5ACCOUNT ID'[Quantity] ),
ALLEXCEPT ( 'TOP5ACCOUNT ID', 'TOP5ACCOUNT ID'[ProductName] )
),
,
DESC,
DENSE
)
),
[top1] = 1
)
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Alex-PBIComm ,
After my test, creating a measure to get the top 1 product name does not work.
I finally achieved it by creating two tables. Because it is written in dax, if your data is updated, the result will also be updated.
TOP5ACCOUNT ID =
FILTER (
SUMMARIZE (
FILTER ( 'Sales(Product Level)', [ProductType] = "GPU" ),
[ProductName],
[Quantity],
"top5",
RANKX (
FILTER ( ALL ( 'Sales(Product Level)' ), [ProductType] = "GPU" ),
CALCULATE (
SUM ( 'Sales(Product Level)'[Quantity] ),
FILTER (
ALLEXCEPT ( 'Sales(Product Level)', 'Sales(Product Level)'[AccountID] ),
[ProductType] = "GPU"
)
),
,
DESC,
DENSE
)
),
[top5] <= 5
)
TOP 1 ProductName =
FILTER (
SUMMARIZE (
'TOP5ACCOUNT ID',
[ProductName],
[Quantity],
"top1",
RANKX (
ALL ( 'TOP5ACCOUNT ID' ),
CALCULATE (
SUM ( 'TOP5ACCOUNT ID'[Quantity] ),
ALLEXCEPT ( 'TOP5ACCOUNT ID', 'TOP5ACCOUNT ID'[ProductName] )
),
,
DESC,
DENSE
)
),
[top1] = 1
)
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Alex-PBIComm ,
So far, I have completed your first request, and I have encountered some difficulties with your second request. Please wait patiently until I come back next week during work hours. I will continue to study.
The measure is
top 5 Accounts per GPU sold =
VAR _RANK =
RANKX (
ALL ( 'Sales(Product Level)' ),
CALCULATE (
SUM ( 'Sales(Product Level)'[Quantity] ),
FILTER (
ALLEXCEPT ( 'Sales(Product Level)', 'Sales(Product Level)'[AccountID] ),
[ProductType] = "GPU"
)
),
,
DESC,
DENSE
)
RETURN
IF ( _RANK <= 5, _RANK )
Because there are only 7 account IDs in my sample data, I returned the top five account IDs.
I apologize again for not solving your problem in time.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Please share a dataset and also show the expected result.
Hi, @Alex-PBIComm
Please try the below measure or similar to the below measure.
Top 1 Product Name on card visual =
MAXX (
TOPN (
1,
'Sales(Product Level)',
CALCULATE (
SUM ( 'Sales(Product Level)'[Quantity] ),
'Sales(Product Level)'[ProductType] = "GPU"
), DESC
),
'Sales(Product Level)'[ProductName]
)
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hello Jihwan,
thanks for your reply! This looks good, but is not considering that I need it only among the top 10 Accounts.
Edit: I've tried it and actually it returns something unexpected, and can't really figure out the reason of the outcome.
Hi, @Alex-PBIComm
Thank you for your feedback.
Please share a dataset and also show the expected result.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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 |
---|---|
109 | |
96 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |