Help needed with Power BI project

Lisa Geiersbach

Hey there! Has anyone created a BI dashboard that shows what percentage of total giving was done by Alumni (whether they were hard or soft credited)?

So, if all gifts totaled $1M and $100K was given by alumni (hard-credit) and another $100K was given by alumni (soft-credit) I'd want it to show that 20% of the total gifts were alumni donations.

I brought in two spreadsheets- #1 was all our alumni with constituent ID and #2 was all the gifts with the donor constituent IDs and the constituents IDs for all soft-credited gifts. I tried tying all ID columns together but that's not getting me anywhere. Am I on the wrong path to do this?

Carol Grant

@Lisa Geiersbach:

Hey there! Has anyone created a BI dashboard that shows what percentage of total giving was done by Alumni (whether they were hard or soft credited)?

So, if all gifts totaled $1M and $100K was given by alumni (hard-credit) and another $100K was given by alumni (soft-credit) I'd want it to show that 20% of the total gifts were alumni donations.

I brought in two spreadsheets- #1 was all our alumni with constituent ID and #2 was all the gifts with the donor constituent IDs and the constituents IDs for all soft-credited gifts. I tried tying all ID columns together but that's not getting me anywhere. Am I on the wrong path to do this?

Hi Lisa- are these spreadsheets from an export you did in Raiser's Edge? You only need one giving export. I do a formula in Power Query but you could do this in a measure also. I add a custom column in Power Query to say if Const Code = “UG Alumni” then put the ConsID.

Then add a measure in DAX to do a Distinct Count on those IDs. Something like this.

CALCULATE(

DISTINCTCOUNT('UG Alumni_Gift_Detail'[ConsID])

'Gift_Detail'[Fiscal Year] = 2024

)

Another way is to run a Constituent Export and a Giving Export. The Constituent export would be giving just from UG Alumni and using Summary Information- Gift Amount for the FY you are looking for and do soft credit to both. Then do another measure that SUMs on the total giving from the gift detail and then divide it by the SUM of the giving from the Constituent Export. It's hard to put this in an email but you need to do measures.

-Carol

Bob Rickards

@Carol Grant Carol, I can explain where I am on our Power BI report with Alumni giving. Not sure if it is correct yet…but it looks good.

First I am using a KPI chart. In that chart, I am using several measures.

AlumGiving% = [AlumGivingNum]/[SolicitableAlumni]

AlumGivingNum = CALCULATE([CountAlumniDonorsTY]+[MarriedAlum]+[NorbertineAlumni])

CountAlumniDonorsTY = CALCULATE( COUNTROWS(FilteredAlumniData),

Filter(FilteredAlumniData,FilteredAlumniData[SumGiftsTY]>0 ))

MarriedAlum = CALCULATE([CountAlumniDonorsTY]*0.123)

LYAlumGiving% = .1205

SolicitableAlumni = CALCULATE(COUNTROWS('Constituent codes'),
FILTER('Constituent codes','Constituent codes'[description]="Alum Undergrad"),

Filter(Constituents,Constituents[deceased] =False),

Filter(Constituents,Constituents[address.inactive]=False) )

AlumGiving%Diff = IF([LYAlumGiving%]>[LYAlumGiving%],[LYAlumGiving%]-[AlumGiving%],[AlumGiving%]-[LYAlumGiving%])

Married alums is a percentage against our overall alumni base. The rest is pretty much math. As I said…this is a start. I will refine as we finish out this year and I pull actual data and compare.

Not sure if this will help…but maybe someone will find this useful.