Grouping a count of specific matching data between 2 tables

[eegarlepp]

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
ID	WhatID	Desciption	Date
A	wer123	TestA	1-May
B	rty456	TestB	2-May
C	rtyu24	TestA	3-May
D	dfg789	TestB	4-May
E	hjk111	TestA	5-May
F	jkrt34	TestB	6-May
G	hjkn12	TestA	7-May
H	hjkn13	TestB	8-May
I	hjkn14	TestA	9-May

Activity Table
ID	Type	Subject	CreatedBY
wer123	Test1	Lorem Ipsum	Eric
wer123	Test2	Lorem Ipsum	Janes
rtyu24	Test1	Lorem Ipsum	Joe
dfg789	Test4	Lorem Ipsum	Joseph
hjk111	Test5	Lorem Ipsum	Eric
jkrt34	Test1	Lorem Ipsum	Janes
dfg789	Test3	Lorem Ipsum	Joe
wer123	Test8	Lorem Ipsum	Joseph
dfg789	Test2	Lorem Ipsum	Eric

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 OPPTYs	Count of Activities
1	2
1	7
1	4
1	11
1	23
1	2
1	3
1	1
1	0
9	53

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 Activity	5	16.67%
2-3 Activities	7	23.33%
4-10 Activities	3	10.00%
11 + Activities	15	50.00%
Total	30	100%

[TomMartens · Accepted solution]

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:

Hopefully this is what you are looking for.

Regards,

Tom

[TomMartens · Accepted solution]

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:

Hopefully this is what you are looking for.

Regards,

Tom

[eegarlepp]

@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!

[TomMartens]

You're welcome!

Tom

[eegarlepp]

@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 Activities	450	70
2-3 Activities	7	21
4-10 Activities	3	30
11 + Activities	15	300
Total	475	421

[TomMartens]

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:

Regards,
Tom

[eegarlepp]

@TomMartens Thank you for all your help. Much appreciated! 🙂

[eegarlepp]

@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 Activities	450	70	$        345,567.00
2-3 Actitvies	7	21	$        145,567.00
4-10 Activities	3	30	$  12,345,567.00
11 + Activies	15	300	$    3,345,567.00
Total	475	421	$  16,182,268.00

[TomMartens · Accepted solution]

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

[eegarlepp]

Thank you Sir! You are a gentleman and scholar for helping with my additional questions. 🙂

[TomMartens]

Hey,

I assume this is what you are looking for:

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

Regards,

Tom