Skip to main content
cancel
Showing results for 
Search instead 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

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
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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