Sum measure with attributes

[GGDAC]

I need help

I have three different tables having common attributes and now i want to create measure summing up all three values from three tables. Below is sample example

Examples

Table - Germany                                    Table -  France                                                          Table - Denmakr

Attribute 1       Attribute 2              Attrubute 1                 Attrubute 2                   Attribute 1                        Attribute2                     New Measure

Budget              Actual                         Budget                             Actual                               Budget                                   Actaul                          Total Acutal

500                    400                              300                                   300                                         200                                     150                                    850

[PaulDBrown]

@GGDAC 

Good evening,

See if this works for you. I've assumed your source resembles this (if the source data has a different structure, please provide an example):

I imported this into PBI, did some transformations in Power Query, involving splitting the tables into two (budget and actuals) and unpivotting. The resulting model looks like this:

And finally built this matrix:

Here is a link to the file if you're interested: PBIX file 

[saraMissBI]

Hi @GGDAC ,

Could you please report your result table?...it is not clear enough

Thank you

[GGDAC]

Hi Sara

Thank you for your reply.

Basically i have three matrix i.e, Sale 1  2 and 3 with attributes Budget and Actual

Now I want to make total of both atrubutes separately as shown in below table

I tried measure with sum but it totals both attribute together though i want budget and actual sepeately

Hope I made little more clearer

Sales 1		Sales 2		Sales 3		Total
Budget	Actual	Budget	Actual	Budget	Actual	Budget	Actual
100	70	200	250	50	40	350	360

[saraMissBI]

Hi @GGDAC ,

Thank you for the details you provided. Computing totals by attribute (Budget, Actual) is automatically done by PowerBI if your data is put together in one table. Please have a look at the snapshot below:

Do you want to have the result in the exact layout your provided in your last post? if yes, could you please provide more details about your model? 

Thank you

[GGDAC]

Hi Sara

My model is like this. I have three markets sales details in matrix and not table

Budget Vs Actual by markets by products

Item	Germany  Budget	Germany Actual	Fraance Budget	France Actual	Denmak Budget	Denmark Actuals	Total Budget	Total Actual
Shoes	100	70	150	120	200	250	450	440
Electronics	50	60	70	70	100	80	220	210
Total	150	130	220	190	300	330	670	650

[V-pazhen-msft]

@GGDAC 

I created the tables and the matrix as you mentioned, and then created two measures. Hope this is what you are looking for.

Total Actual = CALCULATE(SUM(Denmark[Actual])) + CALCULATE(SUM(France[Actual]))+ CALCULATE(SUM(Germany[Actual]))
Total Budget = CALCULATE(SUM(Denmark[Budget])) + CALCULATE(SUM(France[Budget]))+ CALCULATE(SUM(Germany[Budget]))

PBIX:
https://qiuyunus-my.sharepoint.com/:u:/g/personal/paul_qiuyunus_onmicrosoft_com/EesC7JUQ8oNMkXnLUznm...

Paul

[GGDAC]

Dear Paul

Thank you for reply and solution

I tried the measure but it is not working. It gives me error that "Coumn "Actual" in the table can not be found or in may not be used in this expression"

Also i saw you have attached the file but your report is just with three matrix with budget and actuals. The main is creating the measure which is not there in your PBI report though you have give the formual / measure but you have not used in your report

Your report is simply three matrix but not total of all three actuals

Kind regards

Ali

[V-pazhen-msft]

@GGDAC 

Sorry for that, it seems something went wrong that my measures were not show up. I put the measures in the Denmark table.
https://qiuyunus-my.sharepoint.com/:u:/g/personal/paul_qiuyunus_onmicrosoft_com/EesC7JUQ8oNMkXnLUznm... 

The error can be because the table relationship in my sample are different to yours. If you want the absolute solution, I think you could provide a sample pbix.  


Best,
Paul