cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

## Format table to enable value comparison using DAX

 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

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
4 REPLIES 4
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Frequent Visitor

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

Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Frequent Visitor

Thanks very much

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors