DAX - Grouping not return correct Total Value

DAX DAX Calculations
1

Hi Fellow Power BI Users -

I have a table with a number of columns and two distinct date Columns. “Created Date” and "Closed Date"

Close Date is the default relationship to my date dimensions table as this is what a majority of the reporting is based on.

I then needed to create a measure that uses “Date Created” so I can report on all new entries added to the table.

Total Funnel = CALCULATE(sum(Pipeline[Total Amount in USD]),USERELATIONSHIP(Pipeline[Created Date],Dates[Date]),Pipeline[Forecast Category]<>"Omitted")

This returns the correct value for all table entries created in the filter year. e.g. FY19

However, when I use the following DAX express to group the values, I get a different amount.

Funnel Grouping = 
CALCULATE( [Total Funnel],
    Filter(VALUES( Pipeline[Opportunity ID] ),  
        COUNTROWS(
            FILTER('Opportunity Grouping',
                [Total Funnel] >= 'Opportunity Grouping'[Min] &&
                [Total Funnel] < 'Opportunity Grouping'[Max] )) > 0)
)

e.g. Total Funnel = 1000 ; Funnel Grouping = 900.

Investigating the discrepancy between the values I notice that the missing entries excluded from the Funnel Grouping Total, these have a Date Closed outside of the current filter Year (FY19) which is why I suspect the Funnel Grouping Dax measure is not including these.

Please can you advise, how I should modify the Funnel Grouping measure to take account of this so I can include ALL records where the Date Created is in the current Filter Year FY19 no matter what the Date Close is.

Thank you
Neville

2

Can we try something different with this measure as I’m not sure the ‘omitted’ logic can be used like this in the USERELATIONSHIP function. Maybe it can I’m just not sure as have really done it that way before.

Can we try this instead.

Total Amount - Created Date =
     CALCULATE(sum(Pipeline[Total Amount in USD]),
             USERELATIONSHIP(Pipeline[Created Date],Dates[Date]) )

Total Funnel =
     CALCULATE( [Total Amount - Created Date],
             Pipeline[Forecast Category]<>"Omitted")

I just want to see if this improved. I’m not 100% sure, but this is how I would likely lay it out first.

I think it will be easier to solve from here if this doesn’t work also.

Let me know how you go.

3

The above works fine as the “Omitted” part is a filter within Calculate. However, I update your way as its more elegant :slight_smile: but the results are exactly the same.

The issue I’m having is with the Grouping, as its still excluding cases where I have a Closed Date out side for FY19. I should point out that the Page is filter on FY19 [Dates][FY], so maybe I need to update the Grouping filter to ignore that, this is what I think I’ve missed, just not sure where to place it in the below Dax measure?

Funnel Grouping = 
CALCULATE( [Total Funnel],
    Filter(VALUES( Pipeline[Opportunity ID] ),  
        COUNTROWS(
            FILTER('Opportunity Grouping',
                [Total Funnel] >= 'Opportunity Grouping'[Min] &&
                [Total Funnel] < 'Opportunity Grouping'[Max] )) > 0)
)

Nev

4

Yes, this is definitely my problem with the Funnel Group Dax as if I select FY19 & FY20 on the page filter the Total Funnel and Funnel Grouping measures are the same.

So what I need to ensure ALL rows with Created Date of FY19 (Current Page Filter using Dates[FY]) is included no matter what the Closed Date is. I cannot just add ALL(Dates) at the end as this would then included Everything, which I do not required.

Thank you
Nev

5

Oh right, I misread it a little. But yep think it’s probably a little cleaner breaking it out…but also doesn’t matter hugely.

I have a reasonable feeling that the FY filter is the problem, as that will be filtering everything via the active relationship, so the measure won’t be evaluating anything outside that.

This is a tough one…

I think actually it’s this part that need to be adjusted

VALUES( Pipeline[Opportunity ID] )

This list of opportunities is being filtered by the active relationship. So if there is something outside the FY then it’s not being captured and therefore being evaluated.

So what we need to do is maybe adjust this also using USERELATIONSHIP.

It’s hard for me to replicate this so this is just a guess.

What’s if you add this instead of that

CALCULATETABLE( 
        VALUES( Pipeline[Opportunity ID] ),
              USERELATIONSHIP(Pipeline[Created Date],Dates[Date]) )

See how this goes. If not I hope you understand what I’m getting at here, as I’m a little more confident now that’s the problem.

6

Sam to the rescue with CALCULATEABLE,

Thank you it is now representing the data as I need to present it, never used CALCULATETABLE before and this indeed as a great use of this DAX Measure.

Funnel Grouping = 
CALCULATE( [Total Funnel], 
    Filter(CALCULATETABLE(
        VALUES( Pipeline[Opportunity ID] ),
            USERELATIONSHIP(Pipeline[Created Date],Dates[Date]) ),
            COUNTROWS(
                FILTER('Opportunity Grouping',
                    [Total Funnel] >= 'Opportunity Grouping'[Min] &&
                    [Total Funnel] < 'Opportunity Grouping'[Max] )) > 0)
)

I also updated this one, as I needed a count of the records against each group

Count Funnel Grouping = 
    CALCULATE( [Funnel Count], 
        FILTER(CALCULATETABLE(
            VALUES( Pipeline[Opportunity ID] ),
                USERELATIONSHIP(Pipeline[Created Date],Dates[Date]) ),
                COUNTROWS(
                    FILTER('Opportunity Grouping',
                        [Total Funnel] >= 'Opportunity Grouping'[Min] &&
                        [Total Funnel] < 'Opportunity Grouping'[Max] )) > 0)
    )

Thank you
Neville

7

Brilliant that’s great. That was a bit of a guess, but CALCULATETABLE does have some amazing powers.

Check out this video if you want to learn more about it.