Help calculating before/after enhancement % with multiple filters

[mshafik]

pbix download link 

I have some sites with Site ID as the key name. Each of these sites have a certain kpi for each month.

I have done some upgrades to some of the sites at certain dates, and I want to measure the kpi enhancement before and after ("kpi month after upgrade value" divided by "kpi month before upgrade value").

I want to be able to select a single site which is the first part.

Then I want to be able to select multiple "non-continuous" months and see the sum aggregate for site kpis upgraded in selected periods before and after ("sum of total selected site kpis after" divided by "sum of total selected site kpis before").

And I want to be able to do the same using the other slicers shown (Site category, upgrade category, etc...) or a combination of these slicers.

I have been fighting this for the past two days with no success. Can you please help me?

[v-rzhou-msft]

Hi @mshafik 

I am confused about your relationship. As below, you relate Expansions DB 2020 and sitedb by Upg Category and Site ID. They adon't have same values.

I think you want to get the dynamic result by slicer. Final result is kpi month after upgrade value/kpi month before upgrade value. Could you show me more details about your calculate logic?

Such as: When you select 'Site ID' = ALX3007 in Slicer and select Month = xxx, xxx,xxx. 

kpi month after upgrade value = calculate logic A...

kpi month before upgrade value = calculate logic B...

If you can show more details about calculate logic, it will make me easier to understand your requirement.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[mshafik]

Thanks for taking the time to check. I made a mistake in the relation. I wanted to link "Site ID" with "Site ID".

As for the kpi logic, here it is:

kpi month after upgrade = sum of KPI, where Site ID = ALX3007 && Month = selectedMonth + 1

kpi month before upgrade = sum of KPI, where Site ID = ALX3007 && Month = selectedMonth - 1

Someone suggested I can create a claculated table with two calculated columns for KPI month before and KPI month after for eash Site ID. But this is just a sample and I have almost 20 KPIs in the real table. I believe a measure can immmediately do it but I am lost as to how. 

[v-rzhou-msft · Accepted solution]

Hi @mshafik 

I update your relationship in model view. Site ID in slicer is from Expansions DB 2020. We need to disactive the relationship between Date Table and Expansions DB 2020. If we keep the relationship active the measure will get wrong result due to the impact of relationship.

Ex: Keep relationship active, select ALX007, [Y-M] in be filtered by SiteID(ALX007 only has [Y-M] = 2020/02/01), we can only get result by selecting Feb 2020 in Date slicer. When we select other month, result will show blank .

And It is better for us to use both direction between Expansions DB 2020 and sitedb.

Measure:

Measure = 
VAR _SelectSiteID = SELECTEDVALUE('Expansions DB 2020'[Site ID])
VAR _SelectMaxDate = MAX('Date Table'[Date])
VAR _StartPreMonth = EOMONTH(_SelectMaxDate,-2)+1
VAR _EndPreMonth = EOMONTH(_SelectMaxDate,-1)
VAR _kpi_month_before_upgrade = CALCULATE(SUM('kpi'[kpi]),FILTER(ALL('kpi'),'kpi'[Site ID] = _SelectSiteID&&'kpi'[Y-M]>=_StartPreMonth&&'kpi'[Y-M]<=_EndPreMonth))
VAR _StartNextMonth = _SelectMaxDate+1
VAR _EndNextMonth = EOMONTH(_SelectMaxDate,+1)
VAR _kpi_month_after_upgrade = CALCULATE(SUM('kpi'[kpi]),FILTER(ALL('kpi'),'kpi'[Site ID] = _SelectSiteID&&'kpi'[Y-M]>=_StartNextMonth&&'kpi'[Y-M]<=_EndNextMonth))
VAR _ResultKPI = _kpi_month_after_upgrade * _kpi_month_before_upgrade
Return
_ResultKPI

Result is as below.

Ex: Site ID = ALX007, Date = 2020/March.  13.4 is the kpi in 2020/march. 

enhancement = _kpi_month_before_upgrade(11.9 in 2020/Feb) /_kpi_month_after_upgrade(12.9 in 2020/Apr)

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[mshafik]

Thanks a lot.

I will certainly need your help in another question this week. 😃