Forum Discussion
Does Power BI Support Aggregate Awareness?
Does Power BI support aggregate awareness – Aggregate awareness is supported in OBIEE.
Scenario
Sales_Amt | Sales_Amt | Note |
Fact_Sales_Day | Sales_Amt | Sum(Fact_sales_day.sales_amt) <> Fact.sales_month.sales_amt |
Fact_Sales_Month | Sales_Amt | Sum(Fact_sales_month.sales_amt) <> Fact.sales_year.sales_amt |
Fact_Sales_Year | Sales_Amt |
|
|
|
|
Sales Report requirement
Drill Path | Expected Sales_amt |
Year | One row per year & the sales amount = one row in Fact_sales_year |
Year->Month | One row per month & the total sales amount for the year = aggregation of Fact_sales_month for the selected year |
Year->Month->Day | One row per day & the total sales amount for the year = aggregation of Fact_sales_Day for the selected year |
You may create a calendar table and use HASONEVALUE ( 'Calendar'[Date].[Day] ) in nested IF Function to add a measure.
3 Replies
- Greg_Deckler
Community Champion
Not sure if this is the right answer, but Power BI supports context when you use measures. So, you would create a single measure m_Sales_Amt which would have a formula such as SUM(Table[Sales_Amt). Technically, for something this basic, you wouldn't need to use a measure, you could use the default aggregations. Then, as you drill down through, the measure will reflect the current context of the visualization.
Not sure if I've understood . In my case , there are 3 physical fact tables (fact_sales_day ,fact_Sales_month,fact_Sales_year) & three physical columns (sls_amt_day in fact_day, sls_amt_mnth in fact_month ,sls_amt_year , fact_year) . I understand that in OBIEE I can create one logical measure sls_amt & map all the physical measures (sls_amt_day,sls_amt_mnth,sls_amt_year) to logical fact fact_Sales (logical fact sales is mapped to physical fact fact_year,month day). Depending on the drill path , aggregates from respective fact ie if year then fact_year, month then Fact_month & day then fact_Day.
What is the equivalent in Power BI?
v-chuncz-msftCommunity Support
You may create a calendar table and use HASONEVALUE ( 'Calendar'[Date].[Day] ) in nested IF Function to add a measure.
