cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculate sum and share based on a column with repeating values

Hello community!
This is my first post here, hope I do this correctly

I have a huge table with sales data from my customers.

In this table i have several products, product groups, weeks, customers, etc.

I get one row pr. item/customer/week with my revenue, and the total revenue of the Item Group pr. customer and week.

I want to find two things:

1. The total amount of revenue, based on my selection of slicers in my report

2. My share of revenue pr. product

My problem here is that whenever I have a revenye on my Item, the "Total Item group revenue" shows up, and is therefore repeating itself several times.

This is what my table look like, I have only put up one week in this example (mye table contains a lot more columns and rows, but these are the important ones):

 Week Customer Item Item group My revenue Revenue total Item group in store Week 1 North Product A Yellow 50 10 000 Week 1 North Product B Yellow 100 10 000 Week 1 North Product C Yellow 200 10 000 Week 1 North Product D Blue 200 5 000 Week 1 North Product E Blue 50 5 000 Week 1 South Product A Yellow 20 6 000 Week 1 South Product B Yellow 40 6 000 Week 1 South Product C Yellow 50 6 000 Week 1 South Product D Blue 100 1 000 Week 1 South Product E Blue 200 1 000 Week 1 East Product A Yellow 100 1 500 Week 1 East Product B Yellow 150 1 500 Week 1 East Product C Yellow 10 1 500

Regards
Andreas

3 REPLIES 3
Highlighted
Frequent Visitor

## Re: Calculate sum and share based on a column with repeating values

Anybody have a solution for this?

Super User

## Re: Calculate sum and share based on a column with repeating values

Hi,

My total revenue = SUM(Data[My revenue])

Item Group total revenue = MIN(Data[Revenue total Item group in store])

Community Support Team

## Re: Calculate sum and share based on a column with repeating values

Hi @rorand

To make values change with slicer, please consider use a measure instead of calculated column.

would you like the following?

```Measure =
CALCULATE (
SUM ( Sheet2[My revenue] ),
FILTER (
ALLSELECTED ( Sheet2 ),
[Item group] = MAX ( [Item group] )
&& [Customer] = MAX ( [Customer] )
)
)
```

If you select week1, the measure will calculate the total revenue per  Customer for different Item group in week1.

If your requirement is not like this, please show an example for better analysis.

Best Regards

Maggie