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/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.

 
 
Need Help

4 Replies

  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    Hi  xxenoss ,

     

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

    • xxenoss's avatar
      xxenoss
      Helper I
      6 years ago

      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.

       

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