Forum Discussion
Sum by grouped fields
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
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.
4 Replies
- amitchandak
Super User
Anonymous , One to many join between Model and Ocurrencies
Then a measure like
Sumx(Ocurrencies, related(Model[Mileage])
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.
v-yalanwu-msftCommunity Support
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.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
