locked
Behave a group Total as an attribute DAX RRS feed

  • Question

  • Goal: create group total as an attribute using Tabular/DAX

    Scenario: 
    we have: Dim_Product.Color
    With Color attribute names:(A,B,C)
    A Fact_Sales.Counter  is  counting sales for each Color

    Normal pivot output would be:

    A B C GrandTotal
    1 2 3 6


    What I need is to have an 'in between' or group Total of (B+C) that bahaves as the Color attribute with a name of:[BC]
    So not a new measure, but new Color.AB attribute that corresponds to the sum of B+C
    How to create this in DAX: [Color.AB]?

    So the output would be:

    A B C BC GrandTotal
    1 2 3 5 6


    thanks



    • Edited by b_blase Tuesday, November 29, 2016 10:29 AM
    Tuesday, November 29, 2016 10:26 AM

Answers

All replies

  • Hi b_blase,

    We can count sum of B + C, please create a calculated column using the following formula.

    =CALCULATE(SUM(Table[sales]),FILTER(Table,Table[names]="C"||Table[names]="B"))

    While we are not able to display the format you wanted in second picture. Because, the A,B,C return like first picture when you select the attribute names as column level. ColorBC is calculated rather than the values in names column straightly. So the A, B, C, BC are not displayed in same level.

    If you have any question, please feel free to ask.

    Best Regards,
    Angelia

    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, November 30, 2016 6:46 AM
  • Thanks for note, but what you are suggesting is not an calculated column but measure.
    I do not want a  new measure, because than I would need a new measure for each Fact.Measure
    What I need is to create a new DIM Attribute [BC] behaving as a group of Attributes, so called 'in between total'
    Regards

    Wednesday, November 30, 2016 1:13 PM
  • Excel 2016 Pro Plus with PowerPivot and Power Query.
    Create new Color attributes (Brellow & Total) with Power Query.
    http://www.mediafire.com/file/2f7816a5zq4fq41/11_30_16b.xlsx
    http://www.mediafire.com/file/1dya8q1l2qdnh96/11_30_16b.pdf

    Thursday, December 1, 2016 12:41 AM