GroupBy doing multiple calculations

[Anonymous]

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.

[amitchandak]

@Anonymous , You need to create common tables like country, PMN, and region and compare the data across two tables 

[Anonymous]

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

[v-kelly-msft]

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!

[Anonymous]

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,

[v-kelly-msft]

Hi @Anonymous ,

Sorry for the late reply,is below what you need?

If not,pls advise your expected output.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[Anonymous · Accepted 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