Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Hierarchies & Aggregations

Hi,

 

I am trying to achieve Aggregations and Hierarchies, I have 4 tables

 

Product Table

Product Category

Sales Fact

SalesAgg (Aggregated to Product Category)

 

I Product Names roll up to Product Category. So I have an aggregate SalesAgg which points to Product Category.

I need to build a hierarchy on Product Category -> Product Name, so that both Aggregation and Hierarchy work. Please let me know how to achieve this

 

Thanks in advance,

Prashanth

Need Help
Show and Tell
Tips and Tricks
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario.

    Product:

    Product Category:

    Sales Fact:

    Relationships:

     

    You may create a measure or a calculated table as below.

    Measure:
Sales measure = 
var _category = SELECTEDVALUE('Product Category'[Product Category])
return
IF(
    ISINSCOPE('Product'[ProductName]),
    SUM('Product'[Price])*SUM('Product'[Quantity]),
    SUMX(
        FILTER(
            ALL('Product'),
            RELATED('Product Category'[Product Category]) = _category
        ),
        'Product'[Price]*'Product'[Quantity]
    )
)
Calculated table:
SalesAgg = 
SUMMARIZE(
    'Product Category',
    'Product Category'[Product Category],
    "Sales",
    var _category = 'Product Category'[Product Category]
    return
    SUMX(
        FILTER(
            ALL('Product'),
            RELATED('Product Category'[Product Category]) = _category
        ),
        [Price]*[Quantity]
    )
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

9 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    Anonymous why not aggregate to product level?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      parry2k , Product Category is a higher level of Product Names, so Aggregating for performance.

      • parry2k's avatar
        parry2k
        Icon for Super User rankSuper User
        6 years ago

        Anonymous well you can add hierarchy product category -> Product name but when you drill down to product name it is going to non-aggregated table, not sure what actually is the question/issue here?

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario.

    Product:

    Product Category:

    Sales Fact:

    Relationships:

     

    You may create a measure or a calculated table as below.

    Measure:
Sales measure = 
var _category = SELECTEDVALUE('Product Category'[Product Category])
return
IF(
    ISINSCOPE('Product'[ProductName]),
    SUM('Product'[Price])*SUM('Product'[Quantity]),
    SUMX(
        FILTER(
            ALL('Product'),
            RELATED('Product Category'[Product Category]) = _category
        ),
        'Product'[Price]*'Product'[Quantity]
    )
)
Calculated table:
SalesAgg = 
SUMMARIZE(
    'Product Category',
    'Product Category'[Product Category],
    "Sales",
    var _category = 'Product Category'[Product Category]
    return
    SUMX(
        FILTER(
            ALL('Product'),
            RELATED('Product Category'[Product Category]) = _category
        ),
        [Price]*[Quantity]
    )
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi, Anonymous 

     

    If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

     

    Best Regards

    Allan