Summarized table based on dynamic selections

[Shrey03]

Hi All,

Below is my base table -

Date	Column 2	Column 3	Column 4	Column 5
2023-12	C	MF12	0	0
2023-11	C	MF12	0	0
2023-12	E	MF12	0	0
2023-11	E	MF12	0	4835
2023-10	E	MF12	0	4835
2023-10	C	MF12	7615	15454
2023-12	D	MF12	5687	5888
2023-11	D	MF12	7615	15454
2023-10	D	MF12	5687	5888
2023-11	D	AF12	0	6
2023-10	D	AF12	100	35
2023-12	D	AF12	100	6
2023-10	C	AF12	52133	35117
2023-12	C	AF12	75252	65659
2023-11	C	AF12	66480	62601
2023-11	E	AF12	75152	65624
2023-12	E	AF12	66480	62595
2023-10	E	AF12	52033	35111

and below is the expected output (Summarized table by Column 2 & 3 unique value and dynamic date selection)

Date	Column 2	Column 3	Column 4	Column 5	Cal Col1	Cal Col2
2023-10 to 2023-12	C	MF12	7615	15454	102.94	783900
2023-10 to 2023-12	C	AF12	193865	163377	15.73	3048800
2023-10 to 2023-12	D	MF12	18989	27230	43.40	824100
2023-10 to 2023-12	D	AF12	200	47	76.50	15300
2023-10 to 2023-12	E	MF12	0	9670	0.00	0
2023-10 to 2023-12	E	AF12	193665	163330	15.66	3033500
Total Sum			414334	379108		7705600

Formula for: 

Cal Col1=Abs(Column 5 - Column 4)*100/Column 4

Cal Col2=Cal Col1 * Column 4

Can someone please help with a solution to this?

[v-yilong-msft]

Hi @Shrey03 ,
I create a table as you mentioned.

Then I create two calculated columns.

Cal Col1 =
VAR _B =
    ABS ( 'Table'[Column 5] - 'Table'[Column 4] ) * 100 / 'Table'[Column 4]
RETURN
    IF ( 'Table'[Column 4] = 0, 0, _B )

Cal Col2 =
'Table'[Cal Col1] * 'Table'[Column 4]

In the latest version, I think you can manually open the Total value you need.

Best Regards

Yilong Zhou

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

[Shrey03]

Hi @v-yilong-msft,

Thank you for the reply. Can you please help using the base table as below 

to arrive at the highlighted numbers by creating measures as these numbers would be required for further caculations hence having those in Measures would help

[v-yilong-msft]

Hi @Shrey03 ,

Are there some columns provided in image 2 or none at all? If none of them are provided, I don't think it can be realized. This is because when New Table is created, there needs to be a link to create the new table.

Best Regards

Yilong Zhou

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

[Shrey03]

@danextian I understand. My objective is to get the Total Sum values (if possible using measures) and not the entire calculated table which I can further use for my caculations

[danextian]

Hi @Shrey03 ,

Calculated tables don't respond to slicer selections. They are updated only when the refrenced tables are updated, when the formula is modified or upon data refresh. You can use visuals instead to dynamically change the view and filter that using a slicer but the referenced table must have all the rows needed.