Summarize Table

[dianis]

Hello I have the following 3 measures:

Baeline Jan-Jun:

BaselineJanJun = 
var ClientID = SELECTEDVALUE(Data[Client ID])
var Product = SELECTEDVALUE(Data[Product])
var ProjectedMonth = SELECTEDVALUE(Data[Relative Month])
var ProjectedDOM = SELECTEDVALUE(Data[DaysofMonth])
var CurrentMonth = SELECTEDVALUE(Data[Month Key])
var CMMinus1AverageDailyUsage=CALCULATE(SUM(Data[Avg Daily Usage]),
    FILTER(ALL(Data),Data[Relative Month] = "CM-1" && Data[Client ID] = ClientID && Data[Product] = Product))
var CMMinus1AverageDailyMoM=CALCULATE(SUM(Data[Avg Daily Usage Growth MoM %]),
    FILTER(ALL(Data),Data[Relative Month] = "CM-1" && Data[Client ID] = ClientID && Data[Product] = Product))
var Power = SWITCH(ProjectedMonth,"CM",1,"CM+1",2,"CM+2",3,"CM+3",4 ,"CM+4",5)
var Result = ((CMMinus1AverageDailyUsage) * Power((1+CMMinus1AverageDailyMoM),Power))*ProjectedDOM
var Output = IF(and(CurrentMonth>=396,CurrentMonth<=402) && (ProjectedMonth <> "CM-1"),Result,"")
return Output

Baseline Jul-Dec:

BaselineJulDec = 
var ClientID = SELECTEDVALUE(Data[Client ID])
var Product = SELECTEDVALUE(Data[Product])
var ProjectedDOM = SELECTEDVALUE(Data[DaysofMonth])
var CurrentMonth = SELECTEDVALUE(Data[Month Key])
var CMMinus1AverageDailyUsage=CALCULATE(SUM(Data[Avg Daily Usage]),
    FILTER(ALL(Data),Data[Relative Month] = "CM-1" && Data[Client ID] = ClientID && Data[Product] = Product))
var CMMinus1AverageDailyMoM=CALCULATE(SUM(Data[Avg Daily Usage Growth MoM %]),
    FILTER(ALL(Data),Data[Relative Month] = "CM-1" && Data[Client ID] = ClientID && Data[Product] = Product))
var JunDOM = CALCULATE(SUM(Data[DaysofMonth]),
    FILTER(ALL(Data),FORMAT(Data[Month],"MMM") = "Jun" && Data[Client ID] = ClientID && Data[Product] = Product))
var JunBaseline = ((CMMinus1AverageDailyUsage) * Power((1+CMMinus1AverageDailyMoM),5))*JunDOM
var Result = (JunBaseline/JunDOM) * ProjectedDOM
var Output = IF(and(CurrentMonth>=397,CurrentMonth<=402),Result,"")
return Output

Baseline ( Puts together both measures above):

Baseline = 
var CurrentMonth = SELECTEDVALUE(Data[Month Key])
var Output = IF(and(CurrentMonth>=392,CurrentMonth<=396),[BaselineJanJun],[BaselineJulDec])
return Output

Currently the measures are calculated row by row at client and product level.  II am tring to solve 2 issues here:

- The calculation was done based on a small sample of a large data set. When trying to plug in these measures in the large data set i get the following error in the Power formula ( the formula works if I filter a small set of clients ID):

- I am not able to summarize the data at Product or Channel level, as instead of summing up the value of each row in the baseline column and the brin the total value for the channel or product, it attempts to make the calculation at that level of aggregation, hence values are wrong.

Any help needed on how I could adjust these measures to solve the issues above?

Thanks!!

Here you can find teh Power BI ( small subset of a bigger data set)

https://1drv.ms/u/s!AkRGLnYKoqDsjM8jWoS6L0Pwu1n5GA?e=HD0786 

[V-lianl-msft]

Hi @dianis ,

Try to use ALLEXCEPT instead of ALL:

BaselineJanJun = 

var ProjectedMonth = SELECTEDVALUE(Data[Relative Month])
var ProjectedDOM = SELECTEDVALUE(Data[DaysofMonth])
var CurrentMonth = SELECTEDVALUE(Data[Month Key])
var CMMinus1AverageDailyUsage=CALCULATE(SUM(Data[Avg Daily Usage]),
    FILTER(ALLEXCEPT(Data,Data[Client ID],Data[Product]),Data[Relative Month] = "CM-1" ))
var CMMinus1AverageDailyMoM=CALCULATE(SUM(Data[Avg Daily Usage Growth MoM %]),
    FILTER(ALLEXCEPT(Data,Data[Client ID],Data[Product]),Data[Relative Month] = "CM-1"))
var Power = SWITCH(ProjectedMonth,"CM",1,"CM+1",2,"CM+2",3,"CM+3",4 ,"CM+4",5)
var Result = ((CMMinus1AverageDailyUsage) * Power((1+CMMinus1AverageDailyMoM),Power))*ProjectedDOM
var Output = IF(and(CurrentMonth>=392,CurrentMonth<=396) && (ProjectedMonth <> "CM-1"),Result,"")
return Output

BaselineJulDec = 
    
    var ProjectedDOM = SELECTEDVALUE(Data[DaysofMonth])
    var CurrentMonth = SELECTEDVALUE(Data[Month Key])
    var CMMinus1AverageDailyUsage=CALCULATE(SUM(Data[Avg Daily Usage]),
        FILTER(ALLEXCEPT(Data,Data[Client ID],Data[Product]),Data[Relative Month] = "CM-1"))
    var CMMinus1AverageDailyMoM=CALCULATE(SUM(Data[Avg Daily Usage Growth MoM %]),
        FILTER(ALLEXCEPT(Data,Data[Client ID],Data[Product]),Data[Relative Month] = "CM-1" ))
    var JunDOM = CALCULATE(SUM(Data[DaysofMonth]),
        FILTER(ALLEXCEPT(Data,Data[Client ID],Data[Product]),FORMAT(Data[Month],"MMM") = "Jun" ))
    var JunBaseline = ((CMMinus1AverageDailyUsage) * Power((1+CMMinus1AverageDailyMoM),5))*JunDOM
    var Result = (JunBaseline/JunDOM) * ProjectedDOM
    var Output = IF(and(CurrentMonth>=397,CurrentMonth<=402),Result,"")
    return Output

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

[amitchandak]

@dianis , Try to change data type in return as text to use blank() in place or ""

example

var Output = IF(and(CurrentMonth>=396,CurrentMonth<=402) && (ProjectedMonth <> "CM-1"),Result&"","")
return Output

var Output = IF(and(CurrentMonth>=397,CurrentMonth<=402),Result&"","")
return Output

or

var Output = IF(and(CurrentMonth>=396,CurrentMonth<=402) && (ProjectedMonth <> "CM-1"),Result,blank())
return Output

var Output = IF(and(CurrentMonth>=397,CurrentMonth<=402),Result,blank())
return Output