cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
eegarlepp Frequent Visitor
Frequent Visitor

Grouping a count of specific matching data between 2 tables

Somewhat new to Powerbi and am in need of assistance if someone would please help. :-) I have 2 tables in SF, Oppty and Events shown in a small representation below that are linked Via the Oppty, WhatID column and the Activity ID table. They have 100's of thousands of records each. Not every record in the Oppty Table has a record in the Activity table while other records in the Oppty Table may have multiple records in the Activity table.

Oppty Table   
IDWhatIDDesciptionDate
Awer123TestA1-May
Brty456TestB2-May
Crtyu24TestA3-May
Ddfg789TestB4-May
Ehjk111TestA5-May
Fjkrt34TestB6-May
Ghjkn12TestA7-May
Hhjkn13TestB8-May
Ihjkn14TestA9-May

 

Activity Table   
IDTypeSubjectCreatedBY
wer123Test1Lorem IpsumEric
wer123Test2Lorem IpsumJanes
rtyu24Test1Lorem IpsumJoe
dfg789Test4Lorem IpsumJoseph
hjk111Test5Lorem IpsumEric
jkrt34Test1Lorem IpsumJanes
dfg789Test3Lorem IpsumJoe
wer123Test8Lorem IpsumJoseph
dfg789Test2Lorem IpsumEric

 

I am able to represent the data shown below no problem in a PowerBI table and/or matrix and filter it based on the Opptys with more than 2 activities or 3 or 4, etc...-

Count of OPPTYsCount of Activities
12
17
14
111
123
12
13
11
10
953

 

What I would like to do is represent the data so that it groups the Count of Activities based on a certain number of range and display it as below -

Group Count of Oppty With Certain # Of Activities%
0 to 1 Activity516.67%
2-3 Activities723.33%
4-10 Activities310.00%
11 + Activities1550.00%
Total30100%

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Grouping a count of specific matching data between 2 tables

Hey,

 

I couldn't resist and created a little pbix file.

 

This pbix contains two solutions, one is based on calculated columns and one that is based on measures.

Both solutions assume that there is a one-to.many relationship between the tables Oppty (on the one-side) and Activity (on the many-side).

 

I also created a "bin" table that represents the groups. This table is not related to any table.

I created the calculated columns in the Oppty table.

I assigned the measure

ms No Of Activities = 
var NoOfActivities = COUNT(Activity[ID])
return
IF(ISBLANK(NoOfActivities),0,NoOfActivities)

to the table Oppty, whereas the measure

ms grouped no of activities = 
var abt = 
    GROUPBY(
        ADDCOLUMNS(
            VALUES('Opportunity'[ID])
            ,"NoOfActivities",[ms No Of Activities]
        )
        ,[NoOfActivities]
        ,"NoOf",COUNTX(CURRENTGROUP(),'Opportunity'[ID])
    )
return
SUMX(
    VALUES('bins')
    ,
    var binMin = 'bins'[min]
    var binMax = 
        IF(
        NOT(ISBLANK('bins'[max]))
        ,'bins'[max]
        ,MAXX(abt,[NoOfActivities])
        )
    return
    SUMX(
        FILTER(
            abt
            ,[NoOfActivities] >= binMin && [NoOfActivities] <= binMax
        )
        ,[NoOf]
    )
)

is assigned to the table "bins".

Here is a screenshot from the report that I created base on your sample data:

image.png

 

Hopefully this is what you are looking for.

 

Regards,

Tom

 

 

 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Super User
Super User

Re: Grouping a count of specific matching data between 2 tables

Hey,

 

I tweaked my pbix file from above, I added a column "Amount" to the Opportunity table. Please be aware that I also renamed the existing measures  to provide more useful names

 

I also added this measure

Total Amount = 
SUM('Opportunity'[Amount]) 

This is more than a habit, as I tend to avoid to rely on implicit measures, instead I'm creating explicit measures. Nevertheless the important measure is this one that I once again assigned to the bins table:

ms grouped sum of amount = 
var abt = 
    GROUPBY(
        ADDCOLUMNS(
            VALUES('Opportunity'[ID])
            ,"NoOfActivities",[ms No Of Activities]
            --,"SumOfAmount", [Total Amount]
            ,"SumOfAmount", CALCULATE(SUM('Opportunity'[Amount]))
        )
        ,[NoOfActivities]
        ,"SumOf",SUMX(CURRENTGROUP(),[SumOfAmount])
    )
return
SUMX(
    VALUES('bins')
    ,
    var binMin = 'bins'[min]
    var binMax = 
        IF(
            NOT(ISBLANK('bins'[max]))
            ,'bins'[max]
            ,MAXX(abt,[NoOfActivities])
        )
    return
    SUMX(
        FILTER(
            abt
            ,[NoOfActivities] >= binMin && [NoOfActivities] <= binMax
        )
        ,[SumOf]
    )
)

I added the calculation of the amount to the virtual table abt. Then I use this column in the GROUPBY function and also in the final SUMX.

 

Regards,
Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
10 REPLIES 10
Super User
Super User

Re: Grouping a count of specific matching data between 2 tables

Hey,

 

I assume this is what you are looking for:

https://www.daxpatterns.com/static-segmentation/

 

Regards,

Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Super User
Super User

Re: Grouping a count of specific matching data between 2 tables

Hey,

 

I couldn't resist and created a little pbix file.

 

This pbix contains two solutions, one is based on calculated columns and one that is based on measures.

Both solutions assume that there is a one-to.many relationship between the tables Oppty (on the one-side) and Activity (on the many-side).

 

I also created a "bin" table that represents the groups. This table is not related to any table.

I created the calculated columns in the Oppty table.

I assigned the measure

ms No Of Activities = 
var NoOfActivities = COUNT(Activity[ID])
return
IF(ISBLANK(NoOfActivities),0,NoOfActivities)

to the table Oppty, whereas the measure

ms grouped no of activities = 
var abt = 
    GROUPBY(
        ADDCOLUMNS(
            VALUES('Opportunity'[ID])
            ,"NoOfActivities",[ms No Of Activities]
        )
        ,[NoOfActivities]
        ,"NoOf",COUNTX(CURRENTGROUP(),'Opportunity'[ID])
    )
return
SUMX(
    VALUES('bins')
    ,
    var binMin = 'bins'[min]
    var binMax = 
        IF(
        NOT(ISBLANK('bins'[max]))
        ,'bins'[max]
        ,MAXX(abt,[NoOfActivities])
        )
    return
    SUMX(
        FILTER(
            abt
            ,[NoOfActivities] >= binMin && [NoOfActivities] <= binMax
        )
        ,[NoOf]
    )
)

is assigned to the table "bins".

Here is a screenshot from the report that I created base on your sample data:

image.png

 

Hopefully this is what you are looking for.

 

Regards,

Tom

 

 

 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
eegarlepp Frequent Visitor
Frequent Visitor

Re: Grouping a count of specific matching data between 2 tables

@TomMartens Thanks!  I was just replying with how do i connect what you provided and what i was looking for. I just deleted that and will look at the PBIX file and your explanation and get back to you. :-)  Thanks!

Super User
Super User

Re: Grouping a count of specific matching data between 2 tables

You're welcome!

 

Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
eegarlepp Frequent Visitor
Frequent Visitor

Re: Grouping a count of specific matching data between 2 tables

@TomMartens  That worked Perfect!!!!  Thank you! I used the measure option.

 

One last request of your time, if i was to add the actual count of Activities per BIN grouping. How would i go about that? So if there was 100 Opptys that have 2-4 Activities to them, the actual number of Opportunites would be 500 let's say. Below is what i am looking for.

 

Bin Count of Oppty With Certain # Of Activities# of Actual Activities
0 to 1 Activities45070
2-3 Activities721
4-10 Activities330
11 + Activities15300
Total475421
Highlighted
Super User
Super User

Re: Grouping a count of specific matching data between 2 tables

Hey,

 

I just added this measure to the bins table

ms grouped sum of activities = 
var abt = 
    GROUPBY(
        ADDCOLUMNS(
            VALUES('Opportunity'[ID])
            ,"NoOfActivities",[ms No Of Activities]
        )
        ,[NoOfActivities]
        ,"SumOf",SUMX(CURRENTGROUP(),[NoOfActivities])
        
    )
return
SUMX(
    VALUES('bins')
    ,
    var binMin = 'bins'[min]
    var binMax = 
        IF(
            NOT(ISBLANK('bins'[max]))
            ,'bins'[max]
            ,MAXX(abt,[NoOfActivities])
        )
    return
    SUMX(
        FILTER(
            abt
            ,[NoOfActivities] >= binMin && [NoOfActivities] <= binMax
        )
        ,[SumOf]
    )
)

to the bins table. 

Now I'm able to create a table visual using the bin column and the two measures that will look like this:

image.png

 

Regards,
Tom

 

 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
eegarlepp Frequent Visitor
Frequent Visitor

Re: Grouping a count of specific matching data between 2 tables

@TomMartens Thank you for all your help. Much appreciated! :-)

eegarlepp Frequent Visitor
Frequent Visitor

Re: Grouping a count of specific matching data between 2 tables

@TomMartens - My apologies but Ihave one last question. :-)

All is looking good but when I tried to add in the Opportunity SUM amounts i couldn't get it to sum properly. now that I have the counts of Activities bined as well as the # of activities counted per each bin, I wanted to provide a sum of the amount of each of the Opportunities. Each of the Opptys that we counted the activities for had a value, let's call it Amount, how could I provide what the total amounts of each of the groupings? Example below.

 

Bin Count of Oppty With Certain # Of Actvities# of Actual Activties$ Oppty
0 to 1 Activities45070 $        345,567.00
2-3 Actitvies721 $        145,567.00
4-10 Activities330 $  12,345,567.00
11 + Activies15300 $    3,345,567.00
Total475421 $  16,182,268.00
Super User
Super User

Re: Grouping a count of specific matching data between 2 tables

Hey,

 

I tweaked my pbix file from above, I added a column "Amount" to the Opportunity table. Please be aware that I also renamed the existing measures  to provide more useful names

 

I also added this measure

Total Amount = 
SUM('Opportunity'[Amount]) 

This is more than a habit, as I tend to avoid to rely on implicit measures, instead I'm creating explicit measures. Nevertheless the important measure is this one that I once again assigned to the bins table:

ms grouped sum of amount = 
var abt = 
    GROUPBY(
        ADDCOLUMNS(
            VALUES('Opportunity'[ID])
            ,"NoOfActivities",[ms No Of Activities]
            --,"SumOfAmount", [Total Amount]
            ,"SumOfAmount", CALCULATE(SUM('Opportunity'[Amount]))
        )
        ,[NoOfActivities]
        ,"SumOf",SUMX(CURRENTGROUP(),[SumOfAmount])
    )
return
SUMX(
    VALUES('bins')
    ,
    var binMin = 'bins'[min]
    var binMax = 
        IF(
            NOT(ISBLANK('bins'[max]))
            ,'bins'[max]
            ,MAXX(abt,[NoOfActivities])
        )
    return
    SUMX(
        FILTER(
            abt
            ,[NoOfActivities] >= binMin && [NoOfActivities] <= binMax
        )
        ,[SumOf]
    )
)

I added the calculation of the amount to the virtual table abt. Then I use this column in the GROUPBY function and also in the final SUMX.

 

Regards,
Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!