Forum Discussion
GroupBy doing multiple calculations
Hello,
What wouls be the best approach to groupby and calculate values for my data ?
I have 2 tables - Volumes, and TMD:
Volumes:
| Process Date | Call Date | Call Year | Call Month | Month Name | Month | PMN | MoU | MB | TMD.COUNTRY | Std Target |
| 1/1/2021 | 1/1/2021 | 2021 | 202101 | Jan | 1 | AFGAW | 138971.2 | Afghanistan | 5% | |
| 1/1/2021 | 1/1/2021 | 2021 | 202101 | Jan | 1 | AFGTD | 309.1167 | 34219.87 | Afghanistan | 95% |
| 1/2/2021 | 1/1/2021 | 2021 | 202101 | Jan | 1 | AFGAW | 1510.45 | 96262.48 | Afghanistan | 5% |
| 1/2/2021 | 1/1/2021 | 2021 | 202101 | Jan | 1 | AFGEA | 101.6167 | 70.38232 | Afghanistan | 0% |
| 1/2/2021 | 1/1/2021 | 2021 | 202101 | Jan | 1 | AFGTD | 588.2333 | 28468.5 | Afghanistan | 95% |
| 1/3/2021 | 1/1/2021 | 2021 | 202101 | Jan | 1 | AFGAW | 2084.617 | 0 | Afghanistan | 5% |
| 1/3/2021 | 1/1/2021 | 2021 | 202101 | Jan | 1 | AFGEA | 6.717409 | Afghanistan | 0% | |
| 1/3/2021 | 1/1/2021 | 2021 | 202101 | Jan | 1 | CHNCT | 741.25 | 59633.65 | China | 95% |
| 1/3/2021 | 1/1/2021 | 2021 | 202101 | Jan | 1 | CHNCU | 15.33333 | 3617.112 | China | 5% |
| 1/3/2021 | 1/1/2021 | 2021 | 202101 | Jan | 1 | CODOR | 11.78333 | 1.783504 | DRC | 0% |
TMD:
| PMN | Country | Target | Region |
| AFGAR | Afghanistan | 0% | Middle East |
| AFGAW | Afghanistan | 5% | Middle East |
| AFGEA | Afghanistan | 0% | Middle East |
| AFGTD | Afghanistan | 95% | Middle East |
| CHNCT | China | 95% | SE Asia |
| CHNCU | China | 5% | SE Asia |
| CODOR | DRC | 0% | CARIBBEAN |
Total of Partners of Afghanistan : 297999.1612I want to calculate the MBs of each PMN and compare it with Target/Std Target. For calculation wise, lets take only the data of Afghanistan country:
| PMN | Sum of MB | Results | Target |
| AFGTD | 62688.36996 | 4.753659 | 95 |
| AFGAW | 235233.6915 | 1.266822 | 5 |
| AFGEA | 77.09973049 | 3865.113 | 0 |
| AFGAR | 0 | 0 | 0 |
Result = Total of PArtners / Sum of MB
AFGAR is not in Volumes tables, but it is in the TMD table, so needs to be included.
With this calcualted data, I need to show the comparision between Results, and Target showing different color if results is >, <, or == Target. This data needs to be filter by Day wise (Call Date), Month wise, PMN, Country wise - in the new table all these (filter) fields needs to be added ?
How would I write this in DAX ? Could you suggest a type of chart appropariate for such comparissions (somewhere I had seen a chart of kindof 2 bar charts showing the difference, can't get the type right now).
Thanks a lot for your support. Any help is highly appreciated.
Thanks for your effort, v-kelly-msft . I think you have missed to have the calculations by date for each partner of each country.
I got the answer from this thread - Calculations on Summarized columns - Microsoft Power BI Community
Thank you
6 Replies
Anonymous , You need to create common tables like country, PMN, and region and compare the data across two tables
Thanks for your response, amitchandak .
Trying to understand your point - do you mean I need to create seperate tables for Country, Region, Pmn with unique values and use them across my 2 tables. I think, this could be helpful for filtering purpose; but I don't get how I could do this grouping and calculation part with the separate tables. Could you please explain, possibly show some relevant example to understand. Sorry, I am a beginner of Power BI & DAX.
Thank you
Hi Anonymous ,
Create a relationship between the two tables based on column PMN:
Then create a measure as below:
Measure = VAR _MBs = CALCULATE ( SUM ( 'Volumes'[MB] ), FILTER ( ALL ( 'Volumes' ), 'Volumes'[PMN] = MAX ( 'TMD'[PMN] ) ) ) VAR _TotalofPartners = CALCULATE ( SUM ( 'Volumes'[MB] ), FILTER ( ALL ( Volumes ), 'Volumes'[TMD.COUNTRY] = MAX ( 'TMD'[Country] ) ) ) RETURN DIVIDE ( _TotalofPartners, _MBs ) + 0And you will see:
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my reply as a solution!
