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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Unicorn_Tech
Resolver I
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
v-xiaotang
Community Support
Community Support

Hi @Unicorn_Tech 

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.

View solution in original post

3 REPLIES 3
v-xiaotang
Community Support
Community Support

Hi @Unicorn_Tech 

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.

Unicorn_Tech
Resolver I
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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.