Calculate sum and share based on a column with repeating values

[rorand]

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

[v-juanli-msft]

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

[rorand]

Hello again.
Thank you for your replies @Ashish_Mathur and @v-juanli-msft .

I tried your suggestions but still could'nt find a way to solve my problem.
Therefore I have'nt looked at this for some time, but now I am trying to fix this again.

So, I will try to make you better understand what I am trying to fix.

Data
Here is a Dropbox-link to an Excel-sheet with an edited sample of my data.

A short explanation of the two right-most colums in the sheet:

Revenue: shows the revenue for the product at the store for that specific date

Total revenue for Product Group in store:  shows the revenue for the whole product group at the store for that specific date (not only my products, but also my competitors products that are in the same product group.

What I am trying to calculate:

1. The sum of Total revenue for Product Group in store

2. My revenues %-share of Total revenue for Product Group in store

So the problem that I am having trouble with is that I have a lot of products that are in the same category, and therefore the Total revenue for Product Group in store will repeat itself per item that are in the same product group for one store and one specific day.

It is easier to understand if you take a look at my Dropbox-file.

In my original data I also have postal-codes, region and store-chain for each customer, so I also want to be able to aggregate the two measures up and down as i like with filters on postal codes, region, store-chain, product group an product.

To say it easy I want theese measures to work for a single product for each store, and also for all the different levels - both on the customer- and product-side of my data.

Hope this will make you understand better what my problem is, and can help me find a solution 🙂

Best regards

Andreas

[Ashish_Mathur]

Hi,

On the file that you have shared, please show the exact result which you are expecting.

[rorand]

Hello @Ashish_Mathur and thank for your reply.

I have added a Picture-sheet in the Excel-book showing a couple of the visuals I am trying to get.

Also, HERE is a .pbix-file, but this is for now showing the same as in the picture 🙂

Best regards

Andreas

[Ashish_Mathur]

Hi,

I do not understand your requirement.  Share a simple dataset and show the exact expected result in a simple Table format.

[rorand]

Hello again @Ashish_Mathur.

Sorry for my bad explanation and thank you for your patience.

This is what I have and what I am expexting to get, with customer 442 as an example, the two rightmost columns are showing my expected results.

As you can see, it is in the Totals I am having problems:
Red colour = wrong totals

Green colour = correct totals

This is because the total for the Product Group Category is already defined per line on each product.

Date	Customer Name	Product Group	Product Name	Revenue	Total revenue for Product Group in store	My Share	Exact expected result of Total revenue for Product Group in store	Exact expected result of My Share
17.03.2020	Customer 442	Product Group 1	Product Name 137	49	5624	1 %	5624	1 %
17.03.2020	Customer 442	Product Group 1	Product Name 140	55	5624	1 %	5624	1 %
17.03.2020	Customer 442	Product Group 1	Product Name 176	16	5624	0 %	5624	0 %
17.03.2020	Customer 442	Product Group 1	Product Name 178	34	5624	1 %	5624	1 %
17.03.2020	Customer 442	Product Group 1	Product Name 205	21	5624	0 %	5624	0 %
17.03.2020	Customer 442	Product Group 1	Product Name 6	21	5624	0 %	5624	0 %
17.03.2020	Customer 442	Product Group 1	Total	195	33743	1 %	5624	3 %
17.03.2020	Customer 442	Product Group 2	Product Name 4	13800	22914	60 %	22914	60 %
17.03.2020	Customer 442	Product Group 2	Product Name 80	167	22914	1 %	22914	1 %
17.03.2020	Customer 442	Product Group 2	Product Name 82	229	22914	1 %	22914	1 %
17.03.2020	Customer 442	Product Group 2	Product Name 96	22	22914	0 %	22914	0 %
17.03.2020	Customer 442	Product Group 2	Product Name 99	239	22914	1 %	22914	1 %
17.03.2020	Customer 442	Product Group 2	Total	14457	114570	13 %	22914	63 %
17.03.2020	Customer 442	Product Group 5	Product Name 142	38	6565	1 %	6565	1 %
17.03.2020	Customer 442	Product Group 5	Product Name 146	20	6565	0 %	6565	0 %
17.03.2020	Customer 442	Product Group 5	Product Name 182	19	6565	0 %	6565	0 %
17.03.2020	Customer 442	Product Group 5	Total	77	19695	0 %	6565	1 %
17.03.2020	Customer 442	Total		14730	168007	9 %	35103	42 %
17.03.2020	Total			14730	168007	9 %	35103	42 %

Regards

Andreas

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Ashish_Mathur]

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

[rorand]

Anybody have a solution for this?