% Contribution to Sales Growers or Decliners

[Anonymous]

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?

[Icey]

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.

[Anonymous]

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]

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.

[Anonymous]

@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] )

)

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

)

[Icey]

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]

Much closer @Icey Now it is giving me Infinity for any negative result. Also we are getting a number over 100% which should never happen so the formula changed a little. Remember that I don't have sums of negative sales in my pbix but i do for postive sales so maybe I need to fix my Neg sales forumula?

[Icey]

Hi @Anonymous ,

---

Remember that I don't have sums of negative sales in my pbix but i do for postive sales so maybe I need to fix my Neg sales forumula?

---

Oh, I didn't notice this. Please try to fix your Neg sales forumula. Or share me more sample data about calculating [NEG SALES] measure. I'll conduct specific test.

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]

@Icey 

Exactly…for whatever reason in my Excel spreadsheet I didn’t get sums for negative values and I think that is the issue. Should I consider creating these as calculated columns?

[Icey]

Hi @Anonymous ,

If you don't consider the performance factor, you could try it.

And please note that the calculated columns do not change dynamically with slicers or filters. If you need [NEG SALES] and [POS SALES] to change dynamically later, this cannot be achieved.

Best Regards,

Icey

[Anonymous]

@Icey That is not an option so other ideas?

[Icey]

Hi @Anonymous ,

Please create some dummy sample data and share me the calculated logic of [NEG SALES]. Then I can conduct specific test and may provide a better workaround.

Best Regards,

Icey

[Anonymous]

@Icey 

Will do tomorrow...I am not very good at figuring out how to create dummy data but I will work on it.