# Subtotal sum of averages or show only first result of lookupvalue

• ### Question

• Hi

I have imported table from text and want to add with lookupvalue some numbers in order to ID.

For example:

Column A: shop number

Column B: department

Column C: article

Column D: colour

Column E: pieces sold

Column F: square meters of department (lookupvalue)

The problem is that square meters are reapated lot of times and in pivot table there will be sum.

This I can resolve with average but subtotal of square meteres will be correct only for departments and not for whole shop (grand subtotal will calculate also average).

I didn't find any solutions, but was thinking to make something like lookupvalue for only first id and other duplicates fill with 0.

Or maybe there is other easier solution?

Friday, June 29, 2018 3:21 PM

• Hi tomasz.kepa,

>>>Unfortunately your solution in subtotal shows the highest value, not sum...
In this scenario, please make a little change to above DAX formula:

```[Average of SQUARE METERS] :=
SUMX ( VALUES ( 'YourTable'[DEPARTMENT] ), MAX ( 'YourTable'[SQUARE METERS] ) )```

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Wednesday, July 4, 2018 1:01 AM
• Hi tomasz.kepa,

Assuming Average of SQUARE METERS is a measure, then you can try below DAX formula:

```[Average of SQUARE METERS] :=
SUMX ( VALUES ( 'YourTable'[SHOP] ), MAX ( 'YourTable'[SQUARE METERS] ) )```

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

• Proposed as answer by Monday, July 2, 2018 11:28 AM
• Marked as answer by Tuesday, February 19, 2019 5:51 PM
Monday, July 2, 2018 9:57 AM

### All replies

• Excel 2016 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
PQ can transform any format into any other.
For lack of specifics, chose to calculate Sales per Area.
Share file for other solutions.
http://www.mediafire.com/file/fr0gdbobbf32s5k/07_01_18.xlsx/file
http://www.mediafire.com/file/xzjkz8conexgt3t/07_01_18.pdf/file

Monday, July 2, 2018 2:23 AM
• Hi tomasz.kepa,

It is pretty hard to answer this without sample data. To solve your question more efficiently, would you mind typing out 5-10 rows of example data for these table, then showing what results you are expecting based on those sample data? It is much better if you can share the EXCEL work book. Do mask sensitive data before uploading.

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Monday, July 2, 2018 2:43 AM
• https://ibb.co/gwEncJ

A1:F11 example part of database

Column F contains vlookup to other table which looks like K1:N7

E21:E28 is subtotal with average of meteres (they are repeated with vlookup for every article)

Works ok for departments but grandtotal no.

Hope that now is easy to understand.

Monday, July 2, 2018 9:42 AM
• Hi tomasz.kepa,

Assuming Average of SQUARE METERS is a measure, then you can try below DAX formula:

```[Average of SQUARE METERS] :=
SUMX ( VALUES ( 'YourTable'[SHOP] ), MAX ( 'YourTable'[SQUARE METERS] ) )```

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

• Proposed as answer by Monday, July 2, 2018 11:28 AM
• Marked as answer by Tuesday, February 19, 2019 5:51 PM
Monday, July 2, 2018 9:57 AM
• Hi

Unfortunately your solution in subtotal shows the highest value, not sum...

On my screenshot yellow values are those which i would like to see.
Monday, July 2, 2018 12:04 PM
• Hi tomasz.kepa,

>>>Unfortunately your solution in subtotal shows the highest value, not sum...
In this scenario, please make a little change to above DAX formula:

```[Average of SQUARE METERS] :=
SUMX ( VALUES ( 'YourTable'[DEPARTMENT] ), MAX ( 'YourTable'[SQUARE METERS] ) )```

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Wednesday, July 4, 2018 1:01 AM