Need help with a custom measure working on total but not rows

[tktmastr]

Hi all,

I'm trying to create a table with a date slider that combined Pledge and Donation data for an organization.

Sample Data Here: https://drive.google.com/drive/folders/1OwpTUt6DI4Wyx-AsRyawL5NyNkFV1cM7?usp=drive_link

Multiple Donations can be made for one pledge, and customers can have different campaigns they're pledging to.

I'm having trouble with my "Amount Given" measure. It seems to give the correct total but not showing any data on the specific rows.

Both ChatGPT and Gemini keep sending me around in circles and I've spent hours trying different things. Help!

Thanks so much!

[tktmastr]

Thanks @Ashish_Mathur , that worked perfectly and I learned a couple of new things!

@hnguy71 I tried the other approach first but appreciate your response!

[Ashish_Mathur]

You are welcome.

[hnguy71]

Hi @tktmastr 

Your model is a bit incorrect / inconsistent.

You have a customer with two different names. Ideally each customer should be unique with their own names. Additionally, I would create an additional dimension as a master dimension to build a relationship with the pledges and donations together.

 However, with your current model, the following would fix your issue:

Amount Given = 
VAR SelectedDate = MAX('Date'[Date])
VAR _Customer = SELECTEDVALUE(Pledges[CustomerId])
VAR _Campaign = SELECTEDVALUE(Pledges[FullCampaignName])

RETURN

CALCULATE(
    SUM(Donations[Amount]), 
    Donations[CustomerId] = _Customer, 
    Donations[FullCampaignName] = _Campaign,
    ALL(Pledges)
)

However, since the attributes aren't coming from the correct dimension, you'll get a missing total with this measure, in this case, to fix that you'll have to create a secondary measure to total it by row context:

Amount Given Total = SUMX(Pledges, [Amount Given])

Which would give you the following output:

[Ashish_Mathur · Accepted solution]

Hi,

There is a problem with the data.  Customer ID 45875514 is assigned to two different customers.  Correct this anomaly.  After that, create a Many to One and single direction relationship from the CustomerID column of the individual fact tables to the Customers table.  In the 3rd visual of the table, remove CustomerID, First name and Last name.  Drag these columns from the Customers table instead.  I have also revised the amount given measure.  Perform these changes inthe attached file.

Hope this helps.