Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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% |
Solved! Go to 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
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
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 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!
You're welcome!
Tom
@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 |
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
@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 |
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
Hey,
I assume this is what you are looking for:
https://www.daxpatterns.com/static-segmentation/
Regards,
Tom
User | Count |
---|---|
94 | |
83 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
62 |