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

Resolver I

## SUMX total per item, Claculate total per item

Hello.

I have a list of plots, their area, and plant type, and their grower.

I want a column that will total the amount per grower and plant type (I've concatenated a column that gives me a text field of Grower+Planttype.)

total limit measure = calculate(Sum('Total per Grower'[Total Planted Ha Converted]),new_plotinformations[VarietyGrower Concat])
Is giving me the entire total for the table, rather than a total per grower+variety.)

Total limit =
Calculate(
Sum('Total per Grower'[Total Planted Ha Converted]),
ALL(new_plotinformations),
new_plotinformations[VarietyGrower Concat])

Is also giving me a total for the entire table, rather than per grower+Variety.

1 ACCEPTED SOLUTION
Community Support

not sure what's your data like, if you want to total the amount per grower and plant type, maybe you can try ALLEXCEPT() and FILTER() function,

e.g.

measure1 = calculate(sum(table[value]), allexcept(table,table[grower],table[plant type]))

measure2 = calculate(sum(table[value]), filter(table,table[grower]=min(table[grower]) && table[plant type] =min( table[plant type]) ))

If you need more help, please provide more details,

(1) a sample file, you can replace raw data with bogus data to protect your privacy.

or provide some sample data that fully covers your issue/question.

(2) give your expected result based on the sample you provide.

Kindly note: Please ensure the data in sample is concise and representative.

Thanks.

Best Regards,

Community Support Team _Tang

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

3 REPLIES 3
Community Support

not sure what's your data like, if you want to total the amount per grower and plant type, maybe you can try ALLEXCEPT() and FILTER() function,

e.g.

measure1 = calculate(sum(table[value]), allexcept(table,table[grower],table[plant type]))

measure2 = calculate(sum(table[value]), filter(table,table[grower]=min(table[grower]) && table[plant type] =min( table[plant type]) ))

If you need more help, please provide more details,

(1) a sample file, you can replace raw data with bogus data to protect your privacy.

or provide some sample data that fully covers your issue/question.

(2) give your expected result based on the sample you provide.

Kindly note: Please ensure the data in sample is concise and representative.

Thanks.

Best Regards,

Community Support Team _Tang

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

Resolver I

I have also tried this:

totals of the grower = sumx(filter(new_plotinformations,new_plotinformations[VarietyGrower Concat]),new_plotinformations[Total Planted Ha Converted])
Which is giving me the same amoutn for each entry.
Solution Specialist

Hey @Unicorn_Tech.

Try:

``````Total limit measure =
CALCULATE (
SUMX (
VALUES ( new_plotinformations[VarietyGrower Concat] ),
SUM ( 'Total per Grower'[Total Planted Ha Converted] )
)
)``````

## Helpful resources

Announcements

#### Power BI Monthly Update - July 2024

Check out the July 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