locked
Subtotal sum of averages or show only first result of lookupvalue RRS feed

  • 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

Answers

  • Hi tomasz.kepa,

    Thanks for your response.

    >>>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,

    Thanks for your response.

    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 alexander fun Monday, July 2, 2018 11:28 AM
    • Marked as answer by AV111Editor 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,

    Thanks for your question.

    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,

    Thanks for your response.

    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 alexander fun Monday, July 2, 2018 11:28 AM
    • Marked as answer by AV111Editor 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,

    Thanks for your response.

    >>>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