cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
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,

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.

4 REPLIES 4
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,

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.

Helper III

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!

Super User

@mq2020 , Try like

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

A measure can only come in the filter.

Helper III

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?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors