measures by measures in matrix

[Anonymous]

Hi All,

I need to build something like the following:

last row and last column are calculated using other rows and columns.

Is the following something possible to build in powerbi?

Please advise.

[Ashish_Mathur]

Hi,

This depends upon the structure of your data.  Share the link from where i can download your PBI file.

[samdthompson]

Hello, yes thats quite achieveable in a matrix.

1. Drag months on the column field then the three measures in the values section.

2. Alter the formatting for values to show vales on rows once you have added them. 

the Measures will be some thing like

Income = SUM( Table1, Income)

No of Cases = DISTINCTCOUNT( Table1, CaseID)

Incopme /No of Cases= [Income]/[No of Cases]

[Anonymous]

hi Thanks for the reply.

How about the variance column?

[samdthompson · Accepted solution]

Hello, thats a question of what you need to see the the matrix. is it two columns? thats a bit easier since its the difference between the 2. Is it 12 months? if so what difference do you want to see. The variance will be written into measure calculation. The syntax will be something like:

Income =

VAR Income=SUM( Table1, Income)
VAR IncomeFirstMonth = CALCULATE(Income,FIRSTNONBLANK(MonthDate))

VAR IncomeLastMonth= CALCULATE(Income,LASTNONBLANK(MonthDate))

RETURN

IF(HASONEVALUE(Date[Date]),Income,IncomeFirstMonth-IncomeLastMonth))

Have a look here for a really helpful collection of total  calcs which you should be able to rewrite to get what you need: https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907