cancel
Showing results for
Search instead for
Did you mean:
Highlighted
Helper II

Multiply total with %share in another table

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!

4 REPLIES 4
Highlighted
Community Support

Re: Multiply total with %share in another table

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

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Highlighted
Helper II

Re: Multiply total with %share in another tablela

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?

Highlighted
Community Support

Re: Multiply total with %share in another tablela

check this one:

pbix attached

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Highlighted
Helper II

Re: Multiply total with %share in another tablela

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

Helpful resources

Announcements

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors