Hi,
The expression to retrieve top 10 customers based on revenue is as follows.
topCount ([SalesBICube].[All Customers].[By Corporate Group].[Corporate Parent No],10,[Revenue])
How can this expression be tweaked to retrieve records exclusive for North America or Europe?
Please advise.
Vincent
Quote from: vincydza@gmail.com on 03 May 2019 09:59:38 AM
Hi,
The expression to retrieve top 10 customers based on revenue is as follows.
topCount ([SalesBICube].[All Customers].[By Corporate Group].[Corporate Parent No],10,[Revenue])
How can this expression be tweaked to retrieve records exclusive for North America or Europe?
Please advise.
Vincent
Hi,
Rather than using just [Revenue] in the expression, you can use a tuple() of Revenue and the desired Region member instead,
eg
topCount ([SalesBICube].[All Customers].[By Corporate Group].[Corporate Parent No],10,tuple([Your Europe member],[Revenue]))
Cheers!
MF.
MF,
The requirement is to have market segment as a filter prompt for the report like seen below. However the below expression retrieves records with null values.
topCount ([SalesBICube].[All Customers].[By Corporate Group].[Corporate Parent No],10, tuple([SalesBICube].[All Market Segment].[All Market Segment].[Market Segment]->?pMarketSegment? ,[Revenue]))
Vincent
Quote from: vincydza@gmail.com on 03 May 2019 10:36:44 AM
MF,
The requirement is to have market segment as a filter prompt for the report like seen below. However the below expression retrieves records with null values.
topCount ([SalesBICube].[All Customers].[By Corporate Group].[Corporate Parent No],10, tuple([SalesBICube].[All Market Segment].[All Market Segment].[Market Segment]->?pMarketSegment? ,[Revenue]))
Vincent
Is this where there are fewer than 10 customers with a revenue value for a given market segment?
You could use a filter() function to weed these out if necessary
topCount(filter([SalesBICube].[All Customers].[By Corporate Group].[Corporate Parent No], tuple([SalesBICube].[All Market Segment].[All Market Segment].[Market Segment]->?pMarketSegment? ,[Revenue]) is not null),10, tuple([SalesBICube].[All Market Segment].[All Market Segment].[Market Segment]->?pMarketSegment? ,[Revenue]))
Or do you mean something different?
MF.
something different, assuming the report was changed to list only top 2 customers and the attached image was a fact table. Then the report should have a filter on Market segment and list only the Top 2 customers for selected market segment.
If user was to select North America as the filter criteria for the report then it should list only the Top 2 customers for North America. In our example "revenue for Red Bull" and "Coca Cola" should appear on the report.
Vincent
Quote from: vincydza@gmail.com on 07 May 2019 02:21:58 PM
something different, assuming the report was changed to list only top 2 customers and the attached image was a fact table. Then the report should have a filter on Market segment and list only the Top 2 customers for selected market segment.
If user was to select North America as the filter criteria for the report then it should list only the Top 2 customers for North America. In our example "revenue for Red Bull" and "Coca Cola" should appear on the report.
Vincent
Assuming this is a crosstab with Customer Name nested below Market Segment (rather than a list report), the expression to retrieve the Market Segment members would be [SalesBICube].[All Market Segment].[All Market Segment].[Market Segment]->?pMarketSegment?
Otherwise, the previous topCount() expression should be fine for your Customer Name members...
Cheers!
MF.
thanks