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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
mq2020
Helper III
Helper III

Calculate sum of units based on measure value

Hi all,

 

Sorry if this has been asked before, I've looked around for a bit but havent yet found a solution that works for my model.

 

I have a table with sales units and different categories.

I have a measure to rank each category based on the sum of units. (I have it in a measure becasue I didnt manage to get it to work in a column).

 

Now I want to calculate in a measure the % of units that the top rank 1 represents (i.e units if rank =1 / total units).

I tried this formula:

 

% units Top 1 = CALCULATE([Qty],[Rank]=1)

 

but its giving me the following message: A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

 

Any ideas how to go around that? or a different way to do it?

 

**The reason I need it in a measure is beacuse i am using it in the Enlighten Data Story visualization so I can say "top 1 represents xx% of sales".

 
Thanks,
1 ACCEPTED SOLUTION
v-zhenbw-msft
Community Support
Community Support

Hi @mq2020 ,

 

We can use the following measure to meet your requirement.

 

% units Top 1 = 
VAR sum_table =
    SUMMARIZE (
        'Table',
        'Table'[categories],
        "sum", CALCULATE ( SUM ( 'Table'[sales units] ) )
    )
VAR total_units =
    CALCULATE ( SUM ( 'Table'[sales units] ), ALLSELECTED ( 'Table' ) )
RETURN
CALCULATE ( [Qty], FILTER ( sum_table, [rank] = 1 ) ) / total_units

 

The result like this,

 

calculate 1.jpg

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

 

BTW, pbix as attached.

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-zhenbw-msft
Community Support
Community Support

Hi @mq2020 ,

 

We can use the following measure to meet your requirement.

 

% units Top 1 = 
VAR sum_table =
    SUMMARIZE (
        'Table',
        'Table'[categories],
        "sum", CALCULATE ( SUM ( 'Table'[sales units] ) )
    )
VAR total_units =
    CALCULATE ( SUM ( 'Table'[sales units] ), ALLSELECTED ( 'Table' ) )
RETURN
CALCULATE ( [Qty], FILTER ( sum_table, [rank] = 1 ) ) / total_units

 

The result like this,

 

calculate 1.jpg

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

 

BTW, pbix as attached.

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you so much @v-zhenbw-msft 

It worked wonderfully.

 

Just a quick quick regarding the VAR sum_table if you dont mind:

 

(VAR sum_table= SUMMARIZE ( 'Table', 'Table'[categories], "sum", CALCULATE ( SUM ( 'Table'[sales units] ) ) )

 

I've used the Summarize function before to create a table, but I thought it was a static table (i.e if i change the year_quarter in a slicer in the dashboard then the table wouldnt change) -> From what I see in this calculation it does change according to the slicer selection -> Is that only because the Summarize function is being used as a VAR instead of a table?

 

Hope I am explaning myself.

Thanks!

amitchandak
Super User
Super User

Hi @amitchandak 

 

Thank you - it removed the error message however it seems to be giving me the result of total units instead of the ones where rank is 1, not sure why?

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.