Build Categories from a list of items with DAX

[MakZH]

Dear Community,

Maybe some of you already did something similar and can support in this regard.

We have a list of items (see the 1st table), where we have to create categories out of it with the specific totals per category (see the 2nd table). 

I have to create a measure to generate the categories on the fly with DAX and not with the group function to make it more dynamic. 

Thank you for your answers.

[MakZH]

The following calculated table creates an example for "Vegetables" and includes also the sub category "Other", which contains the value of the rest of the substraction of the values of the sub categories of a category.

FarmProducts =

UNION(

    // ----- Category "Vegetable" -----

    ROW("Category","Vegetable","SubCat1","Main","Value",

    CALCULATE(

    SUMX(

        FILTER(

            ALL('Orchard Farm'[Name], 'Orchard Farm'[Value]),

            'Orchard Farm'[Name] = "Vegetable"

        ),

        'Orchard Farm'[Value]

        )

    )),

    ROW("Category","Vegetable","SubCat1","Carrot","Value",

    CALCULATE(

    SUMX(

        FILTER(

            ALL('Orchard Farm'[Name], 'Orchard Farm'[Value]),

            'Orchard Farm'[Name] = "Carrot"

        ),

        'Orchard Farm'[Value]

        )

    )),

    ROW("Category","Vegetable","SubCat1","Zughetti","Value",

    CALCULATE(

    SUMX(

        FILTER(

            ALL('Orchard Farm'[Name], 'Orchard Farm'[Value]),

            'Orchard Farm'[Name] = "Zughetti"

        ),

        'Orchard Farm'[Value]

        )

    )),

    ROW("Category","Vegetable","SubCat1","Other","Value",

    CALCULATE(SUM('Orchard Farm'[Value]),'Orchard Farm'[Name]="Vegetable") - CALCULATE(SUM('Orchard Farm'[Value]), 'Orchard Farm'[Name]="Carrot") - CALCULATE(SUM('Orchard Farm'[Value]),'Orchard Farm'[Name]="Zughetti")

 ))

The result looks like this

[MakZH]

Hi @tamerj1 , may be "dynamic" is to the right expression, but the intention is to build a measure, which can be adjusted with new categories. 

I intend to extend the measure later with the an additional subcategory "other" which contains the difference between the total value of the category and its sub categories.
I.e. based on the example above for "Fruit" we have the value "20000.00" (see table 1) and the total of the available sub categories are "2650.00" (see table 2). So the difference between the total of the category "Fruit" and the sub categories is "17350.00" and will be used for the sub category "other".

[tamerj1]

Hi @MakZH 

csn provide an example that explains the meaning of "more dynamic"?