Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don'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.

Reply
LUCASM
Helper IV
Helper IV

DAX Studio EVALUATE Procedure in Power BI

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 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

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.

 

Jihwan_Kim_1-1664201674224.png

 

 

Jihwan_Kim_0-1664201647193.png

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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

2 REPLIES 2
Jihwan_Kim
Super User
Super User

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.

 

Jihwan_Kim_1-1664201674224.png

 

 

Jihwan_Kim_0-1664201647193.png

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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

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.

 

Top 8 + Others 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.