cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
td_beginner
Helper I
Helper I

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 DateCall DateCall YearCall MonthMonth NameMonthPMNMoUMBTMD.COUNTRYStd Target
1/1/20211/1/20212021202101Jan1AFGAW 138971.2Afghanistan5%
1/1/20211/1/20212021202101Jan1AFGTD309.116734219.87Afghanistan95%
1/2/20211/1/20212021202101Jan1AFGAW1510.4596262.48Afghanistan5%
1/2/20211/1/20212021202101Jan1AFGEA101.616770.38232Afghanistan0%
1/2/20211/1/20212021202101Jan1AFGTD588.233328468.5Afghanistan95%
1/3/20211/1/20212021202101Jan1AFGAW2084.6170Afghanistan5%
1/3/20211/1/20212021202101Jan1AFGEA 6.717409Afghanistan0%
1/3/20211/1/20212021202101Jan1CHNCT741.2559633.65China95%
1/3/20211/1/20212021202101Jan1CHNCU15.333333617.112China5%
1/3/20211/1/20212021202101Jan1CODOR11.783331.783504DRC0%


TMD:

PMNCountryTargetRegion
AFGARAfghanistan0%Middle East
AFGAWAfghanistan5%Middle East
AFGEAAfghanistan0%Middle East
AFGTDAfghanistan95%Middle East
CHNCTChina95%SE Asia
CHNCUChina5%SE Asia
CODORDRC0%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:

PMNSum of MBResultsTarget
AFGTD62688.369964.75365995
AFGAW235233.69151.2668225
AFGEA77.099730493865.1130
AFGAR000

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.

  

1 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

View solution in original post

6 REPLIES 6
amitchandak
Super User
Super User

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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 @td_beginner ,

 

Create a relationship between the two tables based on column PMN:

vkellymsft_0-1629346215357.png

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:

vkellymsft_1-1629346322811.png

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,

 

Hi @td_beginner ,

 

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

vkellymsft_0-1629786934453.png

If not,pls advise your expected output.

 

Best Regards,
Kelly

Did I answer your question? Mark my reply as a 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

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!