Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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

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

Anonymous
Not applicable

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:

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!

Anonymous
Not applicable

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

 

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!

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.