DAX Getting Same Grand Total Amount in Each Row

[BrianNeedsHelp]

If I select 'Current Hierarchy'[Sales Code] and 'Quota Results'[Location Quota], it displays the correct results row by row.  But I have onother table called 'BCP' which has a [Sales Code].  When I select it with Location Quota it gives me the same grand total in each row.   So I created a relationship between 'Current Hierarchy'[Sales Code] and 'BCP'[Sales Code].  But the results are still the same.   Any idea how to fix?  

[DataNinja777 · Accepted solution]

@BrianNeedsHelp ,

The issue of getting the same grand total in each row is typically caused by filter context not propagating correctly between tables. Even though you created a relationship between 'BCP'[Sales Code] and 'Current Hierarchy'[Sales Code], it seems the filter from 'BCP'[Sales Code] is not influencing 'Quota Results'[Location Quota].

To fix this, ensure the relationship is active and propagates correctly through the model. If the relationship alone doesn’t resolve the issue, use DAX functions to enforce the filter. For instance, you can use the TREATAS function to apply 'BCP'[Sales Code] as a filter on 'Current Hierarchy'[Sales Code], ensuring it affects the quota calculation:

Location Quota Adjusted = 
CALCULATE(
    SUM('Quota Results'[Location Quota]),
    TREATAS(
        VALUES('BCP'[Sales Code]),
        'Current Hierarchy'[Sales Code]
    )
)

Alternatively, if the mapping is direct, you can use LOOKUPVALUE to fetch the corresponding Location Quota for each Sales Code in 'BCP'. Validate the filter context with a simple test measure and ensure relationships are correctly configured for your data model.

Best regards,

[BrianNeedsHelp]

I created a new calculated column in BCP to convert the Date Closed to the first day of the month and then joined on the DATE of Calendar 2, and it's working perfect.    This was a HUGE help.  Thanks so much!  

[DataNinja777 · Accepted solution]

@BrianNeedsHelp ,

The issue of getting the same grand total in each row is typically caused by filter context not propagating correctly between tables. Even though you created a relationship between 'BCP'[Sales Code] and 'Current Hierarchy'[Sales Code], it seems the filter from 'BCP'[Sales Code] is not influencing 'Quota Results'[Location Quota].

To fix this, ensure the relationship is active and propagates correctly through the model. If the relationship alone doesn’t resolve the issue, use DAX functions to enforce the filter. For instance, you can use the TREATAS function to apply 'BCP'[Sales Code] as a filter on 'Current Hierarchy'[Sales Code], ensuring it affects the quota calculation:

Location Quota Adjusted = 
CALCULATE(
    SUM('Quota Results'[Location Quota]),
    TREATAS(
        VALUES('BCP'[Sales Code]),
        'Current Hierarchy'[Sales Code]
    )
)

Alternatively, if the mapping is direct, you can use LOOKUPVALUE to fetch the corresponding Location Quota for each Sales Code in 'BCP'. Validate the filter context with a simple test measure and ensure relationships are correctly configured for your data model.

Best regards,

[BrianNeedsHelp]

It Worked! Finally!    I've worked on this for days and days. 
I did have another question, and I can repost but is there a way to apply the Date to this also?  The problem is that I have a  Date column in 'BCP' that has dates throughout the month in the format of like Wednesday, October 4, 2024, but the Location Quota is a monthly amount i.e. Month Year Oct-24.    So I joined 'Calendar 2'[Date] with 'BCP'[Date Closed].  I'm having the same kind of problem it seems with it not influencing or propogating like you mentioned.   The goal would be that any date in e.g. September and October it would show the overall quota in either September or October.  Thank you again, this is amazing.     

[DataNinja777 · Accepted solution]

Hi @BrianNeedsHelp 

I'm glad it worked 😀.

Now, moving onto your 2nd follow up question, to solve the issue of aligning 'BCP'[Date Closed] with 'Quota Results'[Month Year], you need to ensure the date granularity matches and filters propagate correctly. Since 'BCP'[Date Closed] has specific dates and 'Quota Results'[Month Year] uses monthly granularity, use TREATAS in your DAX measure to bridge the gap. Here's the updated measure:

Location Quota Adjusted by Date = 
CALCULATE(
    SUM('Quota Results'[Location Quota]),
    TREATAS(
        VALUES('BCP'[Sales Code]),
        'Current Hierarchy'[Sales Code]
    ),
    TREATAS(
        VALUES('Calendar 2'[Month Year]), 
        'Quota Results'[Month Year]
    )
)

Ensure that 'Calendar 2'[Month Year] matches the format of 'Quota Results'[Month Year], such as "MMM-YY". This measure applies filters for both the Sales Code and the corresponding month based on 'BCP'[Date Closed]. If issues persist, double-check that relationships are active and formats are consistent.

Best regards,