Format table to enable value comparison using DAX

[ndna74]

Month	Name	Balance
August	Loan A	1000
August	Loan B	2000
August	Loan C	2500
August	Loan D	1500
August	Loan E	3000
September	Loan A	1100
September	Loan B	2000
September	Loan C	2600
September	Loan D	1600
September	Loan E	3000

I receive external monthly data and I add it to a table as above. I use a columnar approach rather than matrix. 

Can I use DAX to compare the change in value at loan level and in total? The data will build as the year progresses.

Many thanks

Nick

[v-shex-msft]

Hi @ndna74,

Based on my understanding, you want to get the result of current month balance/ total balance, right?

If it is a case, you can follow below steps to achieve your requirement.

Table:

Measure:

Percent =
var currtemp= LASTNONBLANK(Sheet5[Month],[Month])
return
MAX(Sheet5[Balance])/ SUMX(FILTER(ALL(Sheet5),Sheet5[Month]=currtemp),[Balance])

Format the value to "%":

Create the visual:

Regards,

Xiaoxin Sheng

[ndna74]

Thanks that's really helpful.

Could you also show me how to show the change in value each month? For example, how to show that loan A has increased by 100 between August and September.

Regards

Nick

[v-shex-msft · Accepted solution]

Hi @ndna74,

>>Could you also show me how to show the change in value each month? For example, how to show that loan A has increased by 100 between August and September.

For your requirement, I merge the month and name columns to create a detail name column.

Formula: Table = SELECTCOLUMNS(Sheet2,"Name",CONCATENATE([Month]&"-",[Name]),"Balance",[Balance])

Create a visual with new columns:

Click on "..." button to modify the sort column:

Regards,

Xiaoxin Sheng

[ndna74]

Thanks very much