Multiply total with %share in another table

[Anonymous]

Hi, I have a measure that calculates the total for a certain account number among numerours accounts:  Total EUR Account1 = CALCULATE('GL '[Total EUR],Account[Account number]="1") and then put in a table to show cost per financial quarter:   

FQ1	FQ2	FQ3	FQ4	Total
100	200	400	600	1300

Table 1  

Now this sum per quarter I want to split into four different categories based on another table:

FQ	Category	% share
1	A	10
1	B	30
1	C	40
1	D	20
2	A	50
2	B	10
2	C	10
2	D	30
3	A	25
3	B	25
3	C	25
3	D	25
And so on...

Table 2

So I get the below table where the % share per Category and FQ has been multiplied by the total for the applicable FQ. 

I do not have the categories in the source data for the main table (Table 1)

Category	FQ1	FQ2	FQ3	FQ4	Total
A	10	100	And so on...
B	30	20
C	40	20
D	20	60

Table 3 (wanted result)   

Thankful for any helpful ideas!

[Anonymous]

@amitchandak; any ideas on above?

[Anonymous]

Hi @v-diye-msft 

I now have the follwoing formula which almost gives me the correct result, except for the grand total (please see yellow marked in below picture). Any ides on how that can be fixed?

[v-diye-msft]

Hi @Anonymous 

Kindly check my below results, I do some transformation in the source table and then manage the relationship as well as add the measure:

Pbix attached

[Anonymous]

Hi @v-diye-msft,

Thanks for you help, it seems work partly; the row, column totals and grand total are not correct?

If that can be fixed I think we´re done. Would aprreciate any help on that, thanks!

It also seems I don´t get corect numbers in the table since I acutally also now have years in my data, so I have e.g. FQ 1 2018, FQ 1 2019, FQ 2 2018, FQ 2 2019, and I suspect that I need to adjust the use of MAX-function somehow, but how? 

[v-diye-msft]

Hi @Anonymous 

check this one:

pbix attached

[Anonymous]

Hi @v-diye-msft ,

Thanks a lot, we´re getting closer!

I get the correct totals on FQ-level, but on total year level there is not sum, can I add year to the ISINSCOPE-function somehow? 

I´m also calculating sum last year using the formula;

Measure 4 last year = CALCULATE([Measure 4],SAMEPERIODLASTYEAR('Date table'[Date])) but that does not give me a sum per FQ, any ideas on how to solve that?

and also calculating "% diff this year vs last year", using the formula:

% Diff Measure 4 this year vs last year = DIVIDE([Measure 4 ]-[Measure 4 last year],[Measure 4 last year]) which gives me correct values on category level but not on total FC-level.

/Helena