Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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
@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 ) + 0
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
Thanks @v-kelly-msft. for this solution. It's very clear and I liked the concept of "+ 0 " to the end of Divide().
The 1st layer of grouping should be by Date - all calculations need to be done for each date. If we can achieve that in this solution, then I think the other calculation would be perfect. Any clue to do this for each date for each partner of each country.
Thanks,
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |