Calculating the reason between aggregated rows in different tables

[htaunay]

Hi everyone,

I'm trying to generate numbers that represent the reason between two columns from different tables, but in different timeslices from which the original data was generated. Here is an example for clarity:

Say that in Table A I have the sales numbers from my entire store, separated per hour:

Time	Count
Day 1 - 10AM	10
Day 1 - 12PM	30
Day 1 - 2PM	50
Day 1 - 4PM	10

And that in Table B I have the sales numbers of specific segments of the store, i.e. specific subsets from the total values above separated by segments:

Time	Segment	Count
Day 1 - 10AM	X	2
Day 1 - 12PM	X	4
Day 1 - 2PM	X	10
Day 1 - 4PM	X	4
Day 1 - 10AM	Y	5
Day 1 - 12PM	Y	15
Day 1 - 2PM	Y	10
Day 1 - 4PM	Y	5

I would like to obtain the participation of each segment inside the total sales for the whole day (and evenetually other customizable time slices as well, e.g. months, years, etc).

So for Day 1, it would look something like this:

X's participation in Day 1 = (2 + 4 + 10 + 4) / (10 + 30 +50 +10) => 20%
Y's participation in Day 1 = (5 + 15 + 10 + 5) / (10 + 30 +50 +10) => 25%

Given that the total number of rows will - by definition - be different in this result set, I am assuming that it would be good pratice to create an auxiliary table - in PowerBI only - to generate these numbers. What is not clear to me is which would be the best path to follow in order to obtain this type of result.

Any suggestions are welcome!

Thanks,

Henrique

[Vvelarde · Accepted solution]

hi @htaunay

1: Create a calculated column in both tables:

Date = (DATE(YEAR('Tabla A'[Time]);MONTH('Tabla A'[Time]);DAY('Tabla A'[Time])))

2. Create a measure:

Share = CALCULATE(sum('Tabla B'[Count])/CALCULATE(SUM('Tabla A'[Count])))

3. Put a Table Visual and a Slicer by Date

In table Visual:

Segment

Share

[Eric_Zhang]

@htaunay

In addtion to Vvelarde, do not forget to map a proper relationship between table A and table B.

If you have any question, feel free to let me know.