Forum Discussion

rajannaap's avatar
rajannaap
Regular Visitor
7 years ago
Solved

roling

Hi ,   i need simillar help on my data   i have colums like  Period        divsion   segment    net 201501          a              1              12332 201502          b              2        ...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi rajannaap,

     

    I made one sample for your reference based on your sample data as you shared.  If I misunderstand, kindly share more about that. Here I created a measure to get the avg.

     

    Measure = var mindate = TODAY()-730
var maxdate = TODAY()-365 
var minper = YEAR(mindate)*100+MONTH(mindate)
var maxper = YEAR(maxdate)*100+MONTH(maxdate)
var net = CALCULATE(SUM(Table1[net]),FILTER(Table1,Table1[Period]>=minper&& Table1[Period]<=maxper))
var countr = CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[Period]>=minper&& Table1[Period]<=maxper))
return
net/countr

    Then you can filter the slicer to get the avg you need.

     

    For more details, please check the pbix as attached.

     

    Regards,

    Frank

     

  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi rajannaap,

     

    We can add a what if parameter to work on it. Then create another new measure.

     

    result = [Measure]*Parameter[Parameter Value]

     

    Regards,

    Frank

v-frfei-msft's avatar
v-frfei-msft
Community Support
7 years ago

Hi rajannaap,

 

I made one sample for your reference based on your sample data as you shared.  If I misunderstand, kindly share more about that. Here I created a measure to get the avg.

 

Measure = var mindate = TODAY()-730
var maxdate = TODAY()-365 
var minper = YEAR(mindate)*100+MONTH(mindate)
var maxper = YEAR(maxdate)*100+MONTH(maxdate)
var net = CALCULATE(SUM(Table1[net]),FILTER(Table1,Table1[Period]>=minper&& Table1[Period]<=maxper))
var countr = CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[Period]>=minper&& Table1[Period]<=maxper))
return
net/countr

Then you can filter the slicer to get the avg you need.

 

For more details, please check the pbix as attached.

 

Regards,

Frank

 

  • rajannaap's avatar
    rajannaap
    Regular Visitor
    7 years ago

    Hi

     

     

    once i get the net avg values for each of the divsion and segment, i need to multiply the calcauted avg values of the some of the segments with the constat value. can we create a cells in power Bi so that user can enter the constant values , so that client can get the expected result.

     

    Thanks

    Rajanna

     

    • v-frfei-msft's avatar
      v-frfei-msft
      Community Support
      7 years ago

      Hi rajannaap,

       

      We can add a what if parameter to work on it. Then create another new measure.

       

      result = [Measure]*Parameter[Parameter Value]

       

      Regards,

      Frank