Calculate sum of units based on measure value

[mq2020]

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,

[v-zhenbw-msft · Accepted solution]

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.

[mq2020]

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]

@mq2020 , Try like

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

A measure can only come in the filter.

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

[mq2020]

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?