Pct af totalen

Et eksempel på hvordan man kan beregne pct. af totalsalg og pct af salget på niveauet over det aktuelle.

WITH

MEMBER [Measures].[Pct of total] AS ‘SUM( {[Product].CurrentMember}, [Measures].[Unit Sales]) /SUM( {[Product].DefaultMember}, [Measures].[Unit Sales])’, FORMAT_STRING=’#0.00%’

MEMBER [Measures].[Pct of parent total] AS ‘IIF( [Product].Parent <> NULL, SUM( {[Product].CurrentMember}, [Measures].[Unit Sales]) /SUM( {[Product].Parent.Children}, [Measures].[Unit Sales]), 1)’, FORMAT_STRING=’#0.00%’

SELECT

{ [Measures].[Unit Sales], [Measures].[Pct of Total], [Measures].[Pct of parent total] } ON COLUMNS,

NON EMPTY Order( DESCENDANTS( [Product]  ),[Measures].[Pct of total], DESC ) ON ROWS

FROM

[Sales]

Et eksempel på totalten af en delmængde:

WITH
SET [Categories] AS {[Product].[Product Categories].[Category].&[4], [Product].[Product Categories].[Category].&[1]}
MEMBER [Measures].[Contribution] AS ([Product].[Product Categories].CurrentMember, [Measures].[Sales Amount]) / SUM([Categories], [Measures].[Sales Amount]), FORMAT_STRING=”#,##0.00%”
SELECT
{ [Measures].[Sales Amount], [Measures].[Contribution] } ON COLUMNS
,{[Categories] } ON ROWS
FROM [Adventure Works]

 

Loading

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.