01-18-2019 06:21 AM
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|
01-20-2019 04:26 PM
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])
01-20-2019 10:13 PM
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.