Showing results for 
Search instead for 
Did you mean: 
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




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



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 =
    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



Helpful resources

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 129 members 1,667 guests
Please welcome our newest community members: