cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rorand Frequent Visitor
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 Smiley Happy

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):

WeekCustomerItemItem groupMy revenueRevenue total Item group in store
Week 1NorthProduct AYellow               50           10 000
Week 1NorthProduct BYellow             100           10 000
Week 1NorthProduct CYellow             200           10 000
Week 1NorthProduct DBlue             200            5 000
Week 1NorthProduct EBlue               50            5 000
Week 1SouthProduct AYellow               20            6 000
Week 1SouthProduct BYellow               40            6 000
Week 1SouthProduct CYellow               50            6 000
Week 1SouthProduct DBlue             100            1 000
Week 1SouthProduct EBlue             200            1 000
Week 1EastProduct AYellow             100            1 500
Week 1EastProduct BYellow             150            1 500
Week 1EastProduct CYellow               10            1 500

 

 

Regards
Andreas

3 REPLIES 3
Highlighted
rorand Frequent Visitor
Frequent Visitor

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

Anybody have a solution for this?

Super User
Super User

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

Hi,

 

I am not clear about your question but try these measures

 

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

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

Community Support Team
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.
7.png

 

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

 

Best Regards

Maggie