Sum by grouped fields

[Anonymous]

Hello guys,

I have two tables (Model and Ocurrencies) and I wish to create two measures which sum the mileages of models by the unit field.  In addition, these two tables have a relationship. However, on the Ocurrencies table, the registers must be grouped by the model and month so that distinct registers are considered. Below are my data:

Model table:

Model	Mileage	Unit
Area 1	17000	KM2
Area 2	265,1	KM
Area 3	56,09	KM

Ocurrencies table:

Model	Month
Area 1	January
Area 1	January
Area 1	January
Area 1	February
Area 2	January
Area 2	January
Area 3	March
Area 3	March
Area 3	April

Result when unit equal "KM2":
Model	Month	Mileage
Area 1	January	17000
Area 1	February	17000

Result of the sum measure when unit equal "KM2"

34000

Result when unit equal "KM":
Model	Month	Mileage
Area 2	January	265,1
Area 3	March	56,09
Area 3	April	56,09

Result of the sum measure when unit equal "KM"

377,28

Thank you so much for your help!!

Best wishes,

Gustavo Xerez

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

You could create a measure as follows:

Measure = 
IF(ISINSCOPE('Model'[Model]), 
  CALCULATE( SUM('Model'[Mileage]),FILTER('Ocurrencies',[Month]=MAX('Ocurrencies'[Month]))),
  SUMX(SUMMARIZE('Ocurrencies',[Model],[Month],"1",SUM('Model'[Mileage])),[1]))

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-yalanwu-msft,

Thanks a lot for your help !! I wish to know how to create these two measures as you have done so that one returns the sum when the unit is "KM" and the other one when the unit is "KM2". Both of them will not need a selection of KM or KM2.

Thanks in advance.

Best regards,

Gustavo Xerez

[amitchandak]

@Anonymous , One to many join between Model  and Ocurrencies

Then a measure like 

Sumx(Ocurrencies, related(Model[Mileage])

[Anonymous]

@amitchandak, I have created a measure as you described and unfortunately, it didn´t work. I put the measure in a card and the sum return 68.698,47 using the same data of the post.