Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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,
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
User | Count |
---|---|
25 | |
10 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
9 | |
7 |