How to group, make calculations and then ungroup, possible?

[nor303]

Im trying to make calculations on a group level and then ungroup. Its a little hard to explain so I have made an example in Excel on how I want it done. Im thinking about doing it in a query since I have a folder of excel files which I update quarterly. However if its better to make some new columns or a measure thats also ok, as long as I get it right:) Thanks!

Input
Customer	ID	Loan	Other loans	Value	Collateral	LTV	LTV Intervall
A	11	11	0	12	0	92 %	75-100%
A	13	15	2	27	0	63 %	50-75%
A	15	8	1	20	1	43 %	25-50%
B	21	5	1	8	0	75 %	75-100%
B	23	25	0	55	5	42 %	25-50%
C	33	10	2	18	1	63 %	50-75%
D	41	7	0	25	0	28 %	25-50%
D	43	20	5	37	0	68 %	50-75%
D	45	5	4	6	0	150 %	Above 100%
D	47	14	0	12	1	108 %	Above 100%
Step 1
Customer	ID	Loan	Other loans	Value	Collateral	LTV	LTV Intervall	LTV Group	LTV Intervall Group
A		34	3	59	1			62 %	50-75%
B		30	1	63	5			46 %	25-50%
C		10	2	18	1			63 %	50-75%
D		46	9	80	1			68 %	50-75%
Step 2
Customer	ID	Loan	Other loans	Value	Collateral	LTV	LTV Intervall	LTV Group	LTV Intervall Group
A	11	11	0	12	0	92 %	75-100%	62 %	50-75%
A	13	15	2	27	0	63 %	50-75%
A	15	8	1	20	1	43 %	25-50%
B	21	5	1	8	0	75 %	75-100%	46 %	25-50%
B	23	25	0	55	5	42 %	25-50%
C	33	10	2	18	1	63 %	50-75%	63 %	50-75%
D	41	7	0	25	0	28 %	25-50%	68 %	50-75%
D	43	20	5	37	0	68 %	50-75%
D	45	5	4	6	0	150 %	Above 100%
D	47	14	0	12	1	108 %	Above 100%

[v-heq-msft · Accepted solution]

Hi @nor303 ,

Here some steps that I want to share, you can check them if they suitable for your requirement.

Here is my test data:

Create two measures

LTV Group = 
CALCULATE(
    DIVIDE(SUM('Table'[Loan])+SUM('Table'[Other loans]),SUM('Table'[Value])+SUM('Table'[Collateral])),
    ALLEXCEPT('Table','Table'[Customer])
)

LTV Intervall Group = 
SWITCH(
    TRUE(),
    [LTV Group]<=0.25,"0-25%",
    [LTV Group]>0.25&&[LTV Group]<=0.5,"25-50%",
    [LTV Group]>0.5&&[LTV Group]<=0.75,"50-75%",
    [LTV Group]>0.75&&[LTV Group]<=1,"75-100%",
    [LTV Group]>1,"Above 100%"
)

Final output

Best regards

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[v-heq-msft · Accepted solution]

Hi @nor303 ,

Here some steps that I want to share, you can check them if they suitable for your requirement.

Here is my test data:

Create two measures

LTV Group = 
CALCULATE(
    DIVIDE(SUM('Table'[Loan])+SUM('Table'[Other loans]),SUM('Table'[Value])+SUM('Table'[Collateral])),
    ALLEXCEPT('Table','Table'[Customer])
)

LTV Intervall Group = 
SWITCH(
    TRUE(),
    [LTV Group]<=0.25,"0-25%",
    [LTV Group]>0.25&&[LTV Group]<=0.5,"25-50%",
    [LTV Group]>0.5&&[LTV Group]<=0.75,"50-75%",
    [LTV Group]>0.75&&[LTV Group]<=1,"75-100%",
    [LTV Group]>1,"Above 100%"
)

Final output

Best regards

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[v-heq-msft · Accepted solution]

Hi @nor303 ,
You can test this by following the steps in my pbix file. If my answer is correct, please mark it as accept as solution.

Best regards

Albert He

If this post helps, then please consider Accept it as the solutionton help the other members find it more quickly

[nor303]

One thought; this might be the wrong approach to present this properly. Since If I want to present the Loan values and the group Values the LTV Group will be match only with one loan, and I need to match it to the sum to make it right. So maybe this output would be better:

Step 2
Customer	ID	Loan	Other loans	Value	Collateral	LTV	LTV Intervall	LTV Group	LTV Intervall Group
A	11	11	0	12	0	92 %	75-100%	62 %	50-75%
A	13	15	2	27	0	63 %	50-75%	62 %	50-75%
A	15	8	1	20	1	43 %	25-50%	62 %	50-75%
B	21	5	1	8	0	75 %	75-100%	46 %	25-50%
B	23	25	0	55	5	42 %	25-50%	46 %	25-50%
C	33	10	2	18	1	63 %	50-75%	63 %	50-75%
D	41	7	0	25	0	28 %	25-50%	68 %	50-75%
D	43	20	5	37	0	68 %	50-75%	68 %	50-75%
D	45	5	4	6	0	150 %	Above 100%	68 %	50-75%
D	47	14	0	12	1	108 %	Above 100%	68 %	50-75%