Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi
This is the first time I have wtitten in Dax Studio.
I have written an EVALUATE procedure to give me the Top 8 products and Others with Sales Volumes.
In DAX Studio it appears to work great In Results its just as I expected.
However How do I use this in PowerBI.
You cant copy the code and use it in a Measure and I Cant see anywghere in Power Bi or DAX Studio that links the two together.
I need the table for a chart.
What step am I missing here
Solved! Go to Solution.
Hi,
If you need a measure, please check the below picture and the attached pbix file.
I tried to create a sample pbix file like below.
In my opinion, if you are trying to create a measure, the formula that is created in DAX Studio cannot be simply used by copy-pasting.
Top eight and others: =
VAR _topeightqtytotal =
CALCULATE (
SUM ( Sales[Quantity] ),
TOPN ( 8, ALL ( Category ), CALCULATE ( SUM ( Sales[Quantity] ) ), DESC )
)
VAR _qtyall =
CALCULATE ( SUM ( Sales[Quantity] ), REMOVEFILTERS () )
VAR _topeight =
CALCULATE (
SUM ( Sales[Quantity] ),
KEEPFILTERS (
TOPN ( 8, ALL ( Category ), CALCULATE ( SUM ( Sales[Quantity] ) ), DESC )
)
)
RETURN
IF (
HASONEVALUE ( Category[Category] ),
SWITCH (
TRUE (),
SELECTEDVALUE ( Category[Category] ) = "Others", _qtyall - _topeightqtytotal,
_topeight
),
SUM ( Sales[Quantity] )
)
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.
Hi,
If you need a measure, please check the below picture and the attached pbix file.
I tried to create a sample pbix file like below.
In my opinion, if you are trying to create a measure, the formula that is created in DAX Studio cannot be simply used by copy-pasting.
Top eight and others: =
VAR _topeightqtytotal =
CALCULATE (
SUM ( Sales[Quantity] ),
TOPN ( 8, ALL ( Category ), CALCULATE ( SUM ( Sales[Quantity] ) ), DESC )
)
VAR _qtyall =
CALCULATE ( SUM ( Sales[Quantity] ), REMOVEFILTERS () )
VAR _topeight =
CALCULATE (
SUM ( Sales[Quantity] ),
KEEPFILTERS (
TOPN ( 8, ALL ( Category ), CALCULATE ( SUM ( Sales[Quantity] ) ), DESC )
)
)
RETURN
IF (
HASONEVALUE ( Category[Category] ),
SWITCH (
TRUE (),
SELECTEDVALUE ( Category[Category] ) = "Others", _qtyall - _topeightqtytotal,
_topeight
),
SUM ( Sales[Quantity] )
)
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.
Hi @Jihwan_Kim
thankyou for your reply and help.
The only issue I see with your solution is you assume a category called "Others" which I dont have or I am missreading your solution.
I have around 59 products (C01 to C59) as in your example, and I need to create a table and pie chart - Dont ask!!! of the top 8 products by sales volume this quarter and the other 51 products summed as Others.
I have attached an excel table with same dummy data and a pivot chart showing what I am trying to build.
User | Count |
---|---|
20 | |
14 | |
11 | |
8 | |
6 |
User | Count |
---|---|
23 | |
23 | |
20 | |
15 | |
10 |