Issue in creation of calculated % in Matrix in Power BI

[RacAgr]

Hi ,

We are building a report in power bi .

This is a matrix, where column is YYYY-MM.

It also has 2 other columns 12M and 3M.

Here 3M = ((Current month – Prev 3 Month)/prev 4 month)*100

For example – 3M = (column (2016-07)- column (2016-04)) / column (2016-03)*100

And 12M = ((Current Month – Prev 12 Month)/prev 13 month)*100

For example – 12M = (column (2016-07)- column (2015-07)) / column (2015-06)*100

We are not able to do the above calculations, Can you please us suggest how can this be done. Thanks

[v-shex-msft]

Hi @RacAgr,

You can try to use below formula if them suitable for your requirement:

Sample measure:

CurrMonth=
var currType=LASTNOBLANK(Table[Type],[Type])
reuturn
SUMX(FILTER(ALL(Table),[Type]=currType&&FORMAT([Date],"yyyy-MM")=FORMAT(MAX(Table[Date]),"yyyy-MM")),[Amount]))

3m= 
var currDate=MAX(Table[Date])
var currType=LASTNOBLANK(Table[Type],[Type])
var prev3M=SUMX(FILTER(ALL(Table),[Type]=currType&&FORMAT([Date],"yyyy-MM")=FORMAT(Date(Year(currDate),Month(currDate)-3,1),"yyyy-MM")),[Amount]))
var prev4M=SUMX(FILTER(ALL(Table),[Type]=currType&&FORMAT([Date],"yyyy-MM")=FORMAT(Date(Year(currDate),Month(currDate)-4,1),"yyyy-MM")),[Amount]))
return
(CurrMonth-prev3M)/prev4M*100

12m= 
var currDate=MAX(Table[Date])
var currType=LASTNOBLANK(Table[Type],[Type])
var prev12M=SUMX(FILTER(ALL(Table),[Type]=currType&&FORMAT([Date],"yyyy-MM")=FORMAT(Date(Year(currDate),Month(currDate)-12,1),"yyyy-MM")),[Amount]))
var prev13M=SUMX(FILTER(ALL(Table),[Type]=currType&&FORMAT([Date],"yyyy-MM")=FORMAT(Date(Year(currDate),Month(currDate)-13,1),"yyyy-MM")),[Amount]))
return
(CurrMonth-prev12M)/prev13M*100

If above not help, please share some sample data to test.

Regards,

Xiaoxin Sheng

[RacAgr]

Hello @v-shex-msft

Thanks for the reply. but it does not seem to be working as expected.

attached the same file and expected result.

 

[RacAgr]

Hello all, can someone please help here. Thanks!

[v-shex-msft]

Hi @RacAgr,

I modify the formula based on your sample data, perhaps you can try it if it works on your side.

CurrMonth = 
var currType=LASTNONBLANK(Sheet2[Partner],[Partner])
return
SUMX(FILTER(ALL(Sheet2),[Partner]=currType&&FORMAT(Sheet2[StartDat],"yyyy-MM")=FORMAT(MAX(Sheet2[StartDat]),"yyyy-MM")),[Price])

12m = 
var currDate=MAX(Sheet2[StartDat])
var currType=LASTNONBLANK(Sheet2[Partner],[Partner])
var prev12M=SUMX(FILTER(ALL(Sheet2),[Partner]=currType&&FORMAT([StartDat],"yyyy-MM")=FORMAT(Date(Year(currDate),Month(currDate)-12,1),"yyyy-MM")),[Price])
var prev13M=SUMX(FILTER(ALL(Sheet2),[Partner]=currType&&FORMAT([StartDat],"yyyy-MM")=FORMAT(Date(Year(currDate),Month(currDate)-13,1),"yyyy-MM")),[Price])
return
DIVIDE([CurrMonth]-prev12M,prev13M,BLANK())

3m = 
var currDate=MAX(Sheet2[StartDat])
var currType=LASTNONBLANK(Sheet2[Partner],[Partner])
var prev3M=SUMX(FILTER(ALL(Sheet2),[Partner]=currType&&FORMAT([StartDat],"yyyy-MM")=FORMAT(Date(Year(currDate),Month(currDate)-3,1),"yyyy-MM")),[Price])
var prev4M=SUMX(FILTER(ALL(Sheet2),[Partner]=currType&&FORMAT([StartDat],"yyyy-MM")=FORMAT(Date(Year(currDate),Month(currDate)-4,1),"yyyy-MM")),[Price])
return
DIVIDE([CurrMonth]-prev3M,prev4M,BLANK())

Regards,

Xiaoxin Sheng

[RacAgr]

Thanks @v-shex-msft

I tried to used the same formula mentioned by you for 3M

please find the screen shot below (pC3m)

but the same column is getting repeated for each yyyy-mm. requirement is to have it in the last only.

i am using the matrix 

with partners on rows

YYYYMM_date on columns

and Price and pC3m on  values.

[v-shex-msft]

Hi @RacAgr,

>>but the same column is getting repeated for each yyyy-mm. requirement is to have it in the last only

Current matrix not support to custom the calculation of row/column total. If you add the measure to value field, it will auto generate at each column, I also not find a solution to hide these generated columns.

Regards,

Xiaoxin Sheng