Hi All, Need help with a formula that is for a matrix visual and a Category hierarchy. The last column (CTG/D %) is that rows contribution to either the "parents" sales growers or decliners. It involves determining the row's SALES CHG (Sales for Current year minue Sales for Previous year) is greater than or less that zero and applying the result in either the Sales Growers column or Sales Decliners. Then the CTG/D % columnn calculation is an IF statement for Sales Growth being greater than zero divided by the sum of all sales growers at that level and if Sales Declines is less than zero use Sales Decliners divived by the sum of all sales decliners at that level. The expected results are currently showing in the CTG/D % column. CATEGORYSUBCATSEGMENT SALESSALES % CHG SALES YAGSALES CHG SALES GROWERSSALES DECLINERS CTG/D %CATEGORY1 $6,778,165,6528.4% $6,254,441,133$523,724,519 $551,782,510($28,057,990) 100.0% SUBCAT1 $5,278,695,23210.1% $4,793,055,051$485,640,181 $488,411,107($2,770,925) 88.5% SEGMENT1 $1,375,767,8304.8% $1,313,322,313$62,445,517 $62,445,517$0 12.8% SEGMENT2 $615,239,7874.0% $591,678,506$23,561,281 $23,561,281$0 4.8% SEGMENT3 $188,655,8694.2% $181,111,926$7,543,943 $7,543,943$0 1.5% SEGMENT4 $116,274,582-2.3% $119,045,507($2,770,925) $0($2,770,925) -100.0% SEGMENT5 $1,496,839,84622.9% $1,218,312,822$278,527,024 $278,527,024$0 57.0% SEGMENT6 $248,380,0992.7% $241,850,486$6,529,613 $6,529,613$0 1.3% SEGMENT7 $246,552,7279.2% $225,747,773$20,804,954 $20,804,954$0 4.3% SEGMENT8 $607,840,9107.3% $566,464,407$41,376,503 $41,376,503$0 8.5% SEGMENT9 $383,343,58214.2% $335,721,310$47,622,272 $47,622,272$0 9.8% SUBCAT2 $1,499,470,4192.6% $1,461,386,081$38,084,338 $63,371,403($25,287,065) 11.5% SEGMENT1 $1,327,562,6393.7% $1,280,458,518$47,104,121 $47,104,121$0 74.3% SEGMENT2 $253,85125.3% $202,669$51,182 $51,182$0 0.1% SEGMENT3 $3,613,64461.0% $2,243,909$1,369,735 $1,369,735$0 2.2% SEGMENT4 $24,900,0364.5% $23,836,610$1,063,426 $1,063,426$0 1.7% SEGMENT5 $16,287,510-36.3% $25,561,171($9,273,661) $0($9,273,661) -36.7% SEGMENT6 $1,909,13718.6% $1,609,998$299,139 $299,139$0 0.5% SEGMENT7 $16,955,354-48.6% $32,968,758($16,013,404) $0($16,013,404) -63.3% SEGMENT8 $107,988,24814.3% $94,504,448$13,483,800 $13,483,800$0 21.3% Any ideas?

Hi Icey Thank you, My Sales Growers and Decliners are currently calcuated measures in BI and my version of the Excel table would not have had aggregations correctly. I just mocked up what I wanted. Based on that do you think I should make them be calculated columns like you have done?

Hi Anonymous , Try to repalce SUM ( 'Table'[SALES GROWERS] ) with SUMX('Table',[SALES GROWERS Measure]) and replace SUM ( 'Table'[SALES DECLINERS] ) with SUMX('Table',[SALES DECLINERS Measure]) . Best Regards, Icey If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Icey Thank you! This is getting my closer but still not 100% right. The Sales Growers (POS Sales in the snapshot) look correct but the Sales Decliners (NEG Sales) don't appear to be calculating correctly. I would also like to have the Sales decliners show as a negative percent too. Here is the cleaned up script as well: CTG/D % = VAR SUM_GROWERS =SUMX('tfdatafacts',[POS SALES])VAR SUM_DECLINERS =SUMX('tfdatafacts',[NEG SALES])VAR SUM_SEGMENT_G =CALCULATE ( SUMX('tfdatafacts',[POS SALES]), ALLSELECTED ( 'productmaster'[SEGMENT] ) )VAR SUM_SEGMENT_D =CALCULATE ( SUMX('tfdatafacts',[NEG SALES]), ALLSELECTED ( 'productmaster'[SEGMENT] ) )VAR SUM_SUBCAT_G =CALCULATE ( SUMX('tfdatafacts',[POS SALES]), ALLSELECTED ( 'productmaster'[SUB CATEGORY] ) )VAR SUM_SUBCAT_D =CALCULATE ( SUMX('tfdatafacts',[NEG SALES]), ALLSELECTED ( 'productmaster'[SUB CATEGORY] ) )VAR SUM_CATEGORY_G =CALCULATE ( SUMX('tfdatafacts',[POS SALES]), ALLSELECTED ( 'productmaster'[CATEGORY] ) )VAR SUM_CATEGORY_D =CALCULATE (SUMX('tfdatafacts',[NEG SALES]),ALLSELECTED ( 'productmaster'[CATEGORY] ))RETURNSWITCH (TRUE (),ISFILTERED ( 'productmaster'[SEGMENT] ), IF (SUM_GROWERS > 0,SUM_GROWERS / SUM_SEGMENT_G,- SUM_DECLINERS / SUM_SEGMENT_D),ISFILTERED ( 'productmaster'[SUB CATEGORY] ), IF (SUM_GROWERS > 0,SUM_GROWERS / SUM_SUBCAT_G,- SUM_DECLINERS / SUM_SUBCAT_D),ISFILTERED ( 'productmaster'[CATEGORY] ), IF (SUM_GROWERS > 0,SUM_GROWERS / SUM_CATEGORY_G,- SUM_DECLINERS / SUM_CATEGORY_D),1)

Hi Anonymous , How about this? CTG/D % = VAR SUM_GROWERS = [POS SALES] VAR SUM_DECLINERS = [NEG SALES] VAR SUM_SEGMENT_G = CALCULATE ( [POS SALES], ALLSELECTED ( 'productmaster'[SEGMENT] ) ) VAR SUM_SEGMENT_D = CALCULATE ( [NEG SALES], ALLSELECTED ( 'productmaster'[SEGMENT] ) ) VAR SUM_SUBCAT_G = CALCULATE ( [POS SALES], ALLSELECTED ( 'productmaster'[SUB CATEGORY] ) ) VAR SUM_SUBCAT_D = CALCULATE ( [NEG SALES], ALLSELECTED ( 'productmaster'[SUB CATEGORY] ) ) VAR SUM_CATEGORY_G = CALCULATE ( [POS SALES], ALLSELECTED ( 'productmaster'[CATEGORY] ) ) VAR SUM_CATEGORY_D = CALCULATE ( [NEG SALES], ALLSELECTED ( 'productmaster'[CATEGORY] ) ) RETURN SWITCH ( TRUE (), ISFILTERED ( 'productmaster'[SEGMENT] ), IF ( SUM_GROWERS > 0, SUM_GROWERS / SUM_SEGMENT_G, - SUM_DECLINERS / SUM_SEGMENT_D ), ISFILTERED ( 'productmaster'[SUB CATEGORY] ), IF ( SUM_GROWERS > 0, SUM_GROWERS / SUM_SUBCAT_G, - SUM_DECLINERS / SUM_SUBCAT_D ), ISFILTERED ( 'productmaster'[CATEGORY] ), IF ( SUM_GROWERS > 0, SUM_GROWERS / SUM_CATEGORY_G, - SUM_DECLINERS / SUM_CATEGORY_D ), 1 ) In the previous formula, SUMX is superfluous. Sorry. Best Regards, Icey If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous

Not applicable

6 years ago

% Contribution to Sales Growers or Decliners

Hi All,

Need help with a formula that is for a matrix visual and a Category hierarchy. The last column (CTG/D %) is that rows contribution to either the "parents" sales growers or decliners.

It involves determining the row's SALES CHG (Sales for Current year minue Sales for Previous year) is greater than or less that zero and applying the result in either the Sales Growers column or Sales Decliners. Then the CTG/D % columnn calculation is an IF statement for Sales Growth being greater than zero divided by the sum of all sales growers at that level and if Sales Declines is less than zero use Sales Decliners divived by the sum of all sales decliners at that level. The expected results are currently showing in the CTG/D % column.

CATEGORY	SUBCAT	SEGMENT	SALES	SALES % CHG	SALES YAG	SALES CHG	SALES GROWERS	SALES DECLINERS	CTG/D %
CATEGORY1			$6,778,165,652	8.4%	$6,254,441,133	$523,724,519	$551,782,510	($28,057,990)	100.0%
	SUBCAT1		$5,278,695,232	10.1%	$4,793,055,051	$485,640,181	$488,411,107	($2,770,925)	88.5%
		SEGMENT1	$1,375,767,830	4.8%	$1,313,322,313	$62,445,517	$62,445,517	$0	12.8%
		SEGMENT2	$615,239,787	4.0%	$591,678,506	$23,561,281	$23,561,281	$0	4.8%
		SEGMENT3	$188,655,869	4.2%	$181,111,926	$7,543,943	$7,543,943	$0	1.5%
		SEGMENT4	$116,274,582	-2.3%	$119,045,507	($2,770,925)	$0	($2,770,925)	-100.0%
		SEGMENT5	$1,496,839,846	22.9%	$1,218,312,822	$278,527,024	$278,527,024	$0	57.0%
		SEGMENT6	$248,380,099	2.7%	$241,850,486	$6,529,613	$6,529,613	$0	1.3%
		SEGMENT7	$246,552,727	9.2%	$225,747,773	$20,804,954	$20,804,954	$0	4.3%
		SEGMENT8	$607,840,910	7.3%	$566,464,407	$41,376,503	$41,376,503	$0	8.5%
		SEGMENT9	$383,343,582	14.2%	$335,721,310	$47,622,272	$47,622,272	$0	9.8%
	SUBCAT2		$1,499,470,419	2.6%	$1,461,386,081	$38,084,338	$63,371,403	($25,287,065)	11.5%
		SEGMENT1	$1,327,562,639	3.7%	$1,280,458,518	$47,104,121	$47,104,121	$0	74.3%
		SEGMENT2	$253,851	25.3%	$202,669	$51,182	$51,182	$0	0.1%
		SEGMENT3	$3,613,644	61.0%	$2,243,909	$1,369,735	$1,369,735	$0	2.2%
		SEGMENT4	$24,900,036	4.5%	$23,836,610	$1,063,426	$1,063,426	$0	1.7%
		SEGMENT5	$16,287,510	-36.3%	$25,561,171	($9,273,661)	$0	($9,273,661)	-36.7%
		SEGMENT6	$1,909,137	18.6%	$1,609,998	$299,139	$299,139	$0	0.5%
		SEGMENT7	$16,955,354	-48.6%	$32,968,758	($16,013,404)	$0	($16,013,404)	-63.3%
		SEGMENT8	$107,988,248	14.3%	$94,504,448	$13,483,800	$13,483,800	$0	21.3%

Any ideas?

Need Help

12 Replies

Icey

Community Support

6 years ago

Hi Anonymous ,

Try to create a measure like so:

CTG/D % =
VAR SUM_GROWERS =
    SUM ( 'Table'[SALES GROWERS] )
VAR SUM_DECLINERS =
    SUM ( 'Table'[SALES DECLINERS] )
VAR SUM_SEGMENT_G =
    CALCULATE ( SUM ( 'Table'[SALES GROWERS] ), ALLSELECTED ( 'Table'[SEGMENT] ) )
VAR SUM_SEGMENT_D =
    CALCULATE ( SUM ( 'Table'[SALES DECLINERS] ), ALLSELECTED ( 'Table'[SEGMENT] ) )
VAR SUM_SUBCAT_G =
    CALCULATE ( SUM ( 'Table'[SALES GROWERS] ), ALLSELECTED ( 'Table'[SUBCAT] ) )
VAR SUM_SUBCAT_D =
    CALCULATE ( SUM ( 'Table'[SALES DECLINERS] ), ALLSELECTED ( 'Table'[SUBCAT] ) )
VAR SUM_CATEGORY_G =
    CALCULATE ( SUM ( 'Table'[SALES GROWERS] ), ALLSELECTED ( 'Table'[CATEGORY] ) )
VAR SUM_CATEGORY_D =
    CALCULATE (
        SUM ( 'Table'[SALES DECLINERS] ),
        ALLSELECTED ( 'Table'[CATEGORY] )
    )
RETURN
    SWITCH (
        TRUE (),
        ISFILTERED ( 'Table'[SEGMENT] ), IF (
            SUM_GROWERS > 0,
            SUM_GROWERS / SUM_SEGMENT_G,
            - SUM_DECLINERS / SUM_SEGMENT_D
        ),
        ISFILTERED ( 'Table'[SUBCAT] ), IF (
            SUM_GROWERS > 0,
            SUM_GROWERS / SUM_SUBCAT_G,
            - SUM_DECLINERS / SUM_SUBCAT_D
        ),
        ISFILTERED ( 'Table'[CATEGORY] ), IF (
            SUM_GROWERS > 0,
            SUM_GROWERS / SUM_CATEGORY_G,
            - SUM_DECLINERS / SUM_CATEGORY_D
        ),
        1
    )

BTW, .pbix file attached.

Best Regards,

Icey

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

% Contribution to Sales Growers or Decliners.pbix32 KB

Anonymous
Not applicable
6 years ago
Hi Icey

Thank you, My Sales Growers and Decliners are currently calcuated measures in BI and my version of the Excel table would not have had aggregations correctly. I just mocked up what I wanted. Based on that do you think I should make them be calculated columns like you have done?
- Icey
  Community Support
  6 years ago
  Hi Anonymous ,
  
  Try to repalce
  
  SUM ( 'Table'[SALES GROWERS] )
  
  with
  
  SUMX('Table',[SALES GROWERS Measure])
  
  and replace
  
  SUM ( 'Table'[SALES DECLINERS] )
  
  with
  
  SUMX('Table',[SALES DECLINERS Measure])
  
  .
  
  Best Regards,
  
  Icey
  
  If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

% Contribution to Sales Growers or Decliners

12 Replies

Related content

Contributing to the Community

Calculate contribution to growth

Dashboarding Declined

YTD Quarterly Contribution

Show declining account balances