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.
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
Solved! Go to 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
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
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
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
Thanks very much
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
103 | |
77 | |
66 | |
63 |
User | Count |
---|---|
142 | |
105 | |
102 | |
81 | |
68 |