Forum Discussion

xxenoss's avatar
xxenoss
Helper I
6 years ago

Calculate rolling Measure Percentage

So I know that this topic has been covered in hundred threads, and I probably have read them all, but still cant make it work. I have this data set https://drive.google.com/file/d/1lah5UpgkTd1umJAy...
Need Help
v-xicai's avatar
v-xicai
Community Support
6 years ago

Hi xxenoss ,

 

You may create measure like DAX below.

 

Percentage =

var d=CALCULATE(SUM(Table2[ValueA]),FILTER(ALLSELECTED(Table1), Table1[Date]=MAX(Table1[Date])&&Table1[LocationA]=MAX(Table1[LocationA])&&Table1[LocationB]=MAX(Table1[LocationB])))

return

IF(d<>BLANK(),DIVIDE([Measure], d),0)

 

Best Regards,

Amy 

 

Community Support Team _ Amy

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

xxenoss's avatar
xxenoss
Helper I
6 years ago

Unfortunately that didn’t work for me.

Let me simplify the problem.

 

Table1 contains Date, LacationA, LocationB, Duration, ValueB

Table2 contains Date, Type, LocationB, Duration, ValueA

 

My final table looks like this. Columns Date, LocationB and Duration, are from Bridge tables that connects Table1 & Table2.

And ValueA is function ValueA=IF(SUM(Table1[ValueB])>0, SUM(Table2[ValueA]), BLANK())

 

Lets say I need just Sum of ValueA for this setup. Tipically I would do it like this

 

Measure = IF(SUM(Table1[ValueB])>0, CALCULATE([ValueA], ALLSELECTED(Duration[Duration])), BLANK())

 

 

The problem with it is since there is no LocationA column in Table2, it summarizes ValueA for all the Durations available for specific Date & LocationB in Table2. As you can see in the screenshot result is 856, instead of 728, Cause in Table2 there are additional Durations, that aren’t present in Table1

So I need to filter Durations to those that are available in Table1 for specific Date, LocationA and LocationB.

 

I tried to do it like this, but it doesn’t work

Measure:=IF([Sum of ValueB]>0,
CALCULATE([ValueA], ALLSELECTED(Duration[Duration]), (Table1[LocationA])), BLANK())