SUM()-measure is empty on aggregated level

[jayjay0306]

Hi people,

I hope you can hel me with a complex matter.

I am trying to develop a PBI report which shows the sales effect on a customer when a sales rep have visited this customer.

Now I have the following datamodel:

 

And I need to make a visualization with the following columns/calculations:

"Chain" - group pf customers in the same customer chain

"Customer"  - i.e. customer name

"Calendar Date" - speaks for itself

"Sale"  - same

"Visit date -14" - DAX measure: this measure find the specific visit dates on each customer and SUM() the sale for the last 14 days                                up to this visit date

Visit Date-14 = 
VAR VisitDateToday= MAX('Visits'[Visit Date])
 VAR varReportDate = SELECTEDVALUE( 'Calendar'[date] )
 VAR Visitminus14=
  CALCULATE(
        SUM( 'Sales'[Sales] ),
        FILTER(
            ALL( 'Calendar'[date] ),
            'Calendar'[date] >= varReportDate-14&&'Calendar'[date] < varReportDate
        )
    )
 RETURN
 IFERROR(IF(VisitDateToday,Summinus14,0),BLANK())

"Visit Date+14" - DAX measure: this measure find the specific visit dates on each customer and SUM() the sale for the 14 days                                          following this visit date

Visit Date+14 = 
 VAR VisitDateToday= MAX('Visits'[Visit Date])
 VAR varReportDate = SELECTEDVALUE( 'Calendar'[date] )
 VAR Sumplus14=
  CALCULATE(
        SUM( 'Sales'[Sales] ),
        FILTER(
            ALL( 'Calendar'[date] ),
            'Calendar'[date] <= varReportDate+14&&'Calendar'[date] > varReportDate
        )
    )
    VAR Index_calc=Sumplus14
 RETURN
 IFERROR(IF(VisitDateToday,Index_calc,0),BLANK())

"Visit Date 14 (Index)" - DAX measure: for each customer I calculate the following: DIVIDE("SumPlus14", "SumMinus14") for each visit at the customer.

Visit Date 14 (index) = 
 VAR VisitDateToday= MAX('Visits'[Visit Date])
 VAR varReportDate = SELECTEDVALUE( 'Calendar'[date] )
 VAR Summinus14=
  CALCULATE(
        SUM( 'Sales'[Sales] ),
        FILTER(
            ALL( 'Calendar'[date] ),
            'Calendar'[date] >= varReportDate-14&&'Calendar'[date] < varReportDate
        )
    )
VAR Sumplus14=
    CALCULATE(
        SUM( 'Sale'[Sales] ),
        FILTER(
            ALL( 'Calendar'[date] ),
            'Calendar'[date] <= varReportDate+14&&'Calendar'[date] > varReportDate
        )
    )
    VAR Index_calc=DIVIDE(Sumplus14,Summinus14)
 RETURN
 IFERROR(IF(VisitDateToday,Index_calc,0),BLANK())

"Visit date 14 avg per cust (index)" - DAX measure: finally, for each customer I calculate the average of                         

                             DIVIDE("SumPlus14", "SumMinus14") for all the visit at the customer.

Visit  Date 14 avg pr cust (index) = 
AVERAGEX(
    CALCULATETABLE(VALUES('Calendar'[date]), ALLSELECTED('Calendar'[date]), 'Visits'[Visit Date]),
    VAR varReportDate = CALCULATE(MAX('Calendar'[date]))
    VAR Summinus14=
     CALCULATE(
            SUM( 'Sales'[Sales]),
            FILTER(
                ALL( 'Calendar'[date] ),
                'Calendar'[date] >= varReportDate-14&&'Calendar'[date] < varReportDate
            )
        )
    VAR Sumplus14=
        CALCULATE(
            SUM( 'Sales'[Sales] ),
            FILTER(
                    ALL( 'Calendar'[date] ),
                    'Calendar'[date] <= varReportDate+14&&'Calendar'[date] > varReportDate
            )
        )
    RETURN 
  DIVIDE(Sumplus14,Summinus14)
)

As long as I calculate at customer level the calculations works and I get this end-result:

 

BUT, and here is my challenge, If I want to see the calculation "Visit date 14 avg per cust (index)" on "Chain"-level, and hence add this to the visualization, this happens:

And I can't figure out why or where to correct it. As I can see the problem is that these measures turns to "Visit date -14"=0 and "Visit date +14"=0. And the reason for this is that these measure for some reason only works at customer level?

Therfore, Can anyone tell me how to change these, so I keep the values on the customer visits while adding the Chain-level?

It will be greatly appreciated.

BR,

Jayjay0306

[lbendlin]

Have you considered using a graphical solution instead?  That way you could see quickly if and how the visits have influenced sales.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[jayjay0306]

thanks Ibendlin, but I believe I have solved the problem now.

Br,

jayjay0306