Subtotal in a visual table returning zero on a measure column

Anonymous — Wed, 23 Aug 2023 19:49:49 GMT

Hi,

I have created a visual table and I would like to get the total sum from a column that I inserted as a measure,

The table keeps returning me 0, but it is expected to make a sum of each row from this visual table,

I can get my result by exporting this to excel this table and then making the sum manually, but I would like to get this result automatically, this result could be a simple measure to insert in a visual card, for example,

(This number is just an example, I couldn't get the right formula to it, and that's where I need help)

I need help to write a measure to get this result and insert in a visual card, I know that is impossible to get my expected result in the visual table column subtotal,

Let me explain the composition of this data here by using examples:

(This is a fixed key table)

Table1
CodeID
800717
800275
800284
800277
800288
800283
800909
800272
800473

(This is a dimension table, it might have some IDs from Table1)

Table2
CodeID	Value
800717	224,2
800275	281,1
800284	351,3
800277	204,1

(This is a dimension table, it might have some IDs from Table1)

Table3
CodeID	Value
800717	287,1
800275	297,2
800288	223,2
800283	245,3
800909	256,7
800272	257,5
800473	210,2
AVERAGE	253,9

(Then I create this visual table, by using the Table1 as structure, and values from table 2 and 3, plus the measures)

=Data from Table1			=IF( ISBLANK([ValuesTable3]), ValuesTable2, ValuesTable3)	=[Measure1] - CALCULATE( AVERAGE(Value'Table3'), ALLSELECTED('Table3'))		= [FixedValue] * [Measure2]
CodeID	ValuesTable2	ValuesTable3	Measure1	Measure2	FixedValue	Measure3
800717	224,2	287,1	287,1	33,21428571	2469,2	82012,71
800275	281,1	297,2	297,2	43,31428571	4697,2	203455,86
800284	351,3		351,3	97,41428571	3001,1	292350,01
800277	204,1		204,1	-49,78571429	2002,4	-99690,91
800288		223,2	223,2	-30,68571429	6447,2	-197836,94
800283		245,3	245,3	-8,585714286	946,9	-8129,81
800909		256,7	256,7	2,814285714	876,3	2466,16
800272		257,5	257,5	3,614285714	1621,7	5861,29
800473		210,2	210,2	-43,68571429	784,6	-34275,81
SUBTOTAL		253,89	253,89	0	22846,6	0 (Expected result here should be 246212,56)

Thank you for your support, community,

Re: Subtotal in a visual table returning zero on a measure column

Anonymous — Fri, 25 Aug 2023 01:53:00 GMT

HI Anonymous,

It seems like a common issue when measure expression calculate with multiple level aggregations, you can take a look the following blog to know more about this and how to handle it:

Measure Totals, The Final Word

Regards,

Xiaoxin Sheng

topic Re: Subtotal in a visual table returning zero on a measure column in DAX Commands and Tips

Subtotal in a visual table returning zero on a measure column

Re: Subtotal in a visual table returning zero on a measure column