Calculate rolling Measure Percentage

[xxenoss]

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/1lah5UpgkTd1umJAyPDGmPd7HHujH_a6h/view?usp=sharing 

There are two table connected with bridge tables. I have created a measure that summarizes ValueB for 11 rolling days.

Measure = IF(SUM(Table2[ValueA])>0;

CALCULATE( SUM(Table1[ValueB]); Table2[Type]="TypeX"; FILTER(ALL(DateTable); DateTable[Date]>=MAX(DateTable[Date])-5 && DateTable[Date]<=MAX(DateTable[Date])+5)); BLANK())

What I need is Percentage of this Measure for this set. 127 to become 0,1591478696741855, 143 - 0,1791979949874687, etc.

[v-xicai]

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]

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())

[MFelix]

Hi  @xxenoss ,

The percentages you are calculating are based on what values? Can you share how they are calculated?

[xxenoss]

Calculation is as follows

Measure as it is shown in the example devided by sum of Measure For specific Date, LocatoinA, LocationB, but not Duration.

For example in the screenshot in will be 127/798, 143/798, 131/798. Basically I need to find Sum of Measure and the devide Mesure by its SUM.