DAX Formula for Stacked CHart

[Ramagopal]

I need to make a stacked column chart where percentages are shown in the tooltips. I am having issues displaying percentages correctly. Data in excel shared

% TVR by Promo =

VAR CurrentMonth = SELECTEDVALUE(New_Dash_Board[month])

VAR CurrentSpotType = SELECTEDVALUE(New_Dash_Board[Type])

VAR CurrentPromo = SELECTEDVALUE(New_Dash_Board[Promotional])

VAR Numerator =

    SUM(New_Dash_Board[sum(tvr)])

VAR Denominator =

    CALCULATE(

        SUM(New_Dash_Board[sum]),

        FILTER(

            ALL(New_Dash_Board),

            New_Dash_Board[month] = CurrentMonth &&

           New_Dash_Board[Type] = CurrentSpotType

        )

    )

RETURN

DIVIDE(Numerator, Denominator, 0)... Using this I am continuosly getting the denominator same as the numerator and percantage coming as 100%...

Can any one suggest me where I need to correct my logic

[V-yubandi-msft]

Hi @Ramagopal ,

Could you let us know if your issue has been resolved or if you need any more information. We're here to help if you need further assistance.

Thank you.

[V-yubandi-msft]

Hi @Ramagopal ,

If you get a chance, please review the response shared by @MFelix , @krishnakanth240 and let us know if it aligns with your expectations. Should you need any additional details or clarification, feel free to let us know.

Thank you for your valuable input @krishnakanth240, @MFelix .

Regards,
Yugandhar.

[krishnakanth240]

Hi @Ramagopal 

Can you try this measure

% TVR by Promo =

VAR CurrentMonth = SELECTEDVALUE(New_Dash_Board[month])

VAR CurrentSpotType = SELECTEDVALUE(New_Dash_Board[Type])

VAR Numerator = SUM(New_Dash_Board[sum(tvr)])

VAR Denominator = CALCULATE(

SUM(New_Dash_Board[sum(tvr)]),

FILTER(ALLEXCEPT(New_Dash_Board,

New_Dash_Board[month], New_Dash_Board[Type]),

New_Dash_Board[month] = CurrentMonth && New_Dash_Board[Type] = CurrentSpotType))

RETURN

DIVIDE(Numerator, Denominator, 0)

[MFelix · Accepted solution]

Hi @Ramagopal ,

For this you need to use a measure to change the different levels of the scope try the following code:

% TVR by Promo =
		VAR CurrentSpotType = SELECTEDVALUE('New_Dash_Board'[Type])
		VAR CurrentSpotMother = SELECTEDVALUE('New_Dash_Board'[Mother])
		VAR CurrentPromo = SELECTEDVALUE('New_Dash_Board'[Promotional])

		VAR Numerator =
		SUM('New_Dash_Board'[sum(tvr)])

		VAR Denominator =
		SWITCH(
			TRUE(),
			ISINSCOPE(New_Dash_Board[Promotional]), CALCULATE(
				SUM('New_Dash_Board'[sum(tvr)]),
				REMOVEFILTERS(New_Dash_Board[Promotional]),
				New_Dash_Board[Type] = CurrentSpotType
			),

			ISINSCOPE(New_Dash_Board[Type]), CALCULATE(
				SUM('New_Dash_Board'[sum(tvr)]),
				REMOVEFILTERS(New_Dash_Board[Type]),
				New_Dash_Board[Mother] = CurrentSpotMother
			),
			ISINSCOPE(New_Dash_Board[Mother]), CALCULATE(
				SUM('New_Dash_Board'[sum(tvr)]),
				REMOVEFILTERS(New_Dash_Board[Mother])
			)
		)


		RETURN
			DIVIDE(
				Numerator,
				Denominator,
				0
			)

An easier option is to use a visual calculation that way you only need to write the following code:

TVR % = DIVIDE([Total] ,CALCULATE([Total] ,COLLAPSE(ROWS,1 )))

Has you can see result is the same:

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-visual-calculations-overview