cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
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
Community Support

Re: Multiply total with %share in another table

Hi @helenawickstrom 

 

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

8.PNG

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
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
Community Support

Re: Multiply total with %share in another tablela

Hi @helenawickstrom 

 

check this one:

008.PNG

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

Get Ready for Power BI Dev Camp

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

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

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