Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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...
thanks Ibendlin, but I believe I have solved the problem now.
Br,
jayjay0306