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 !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

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!