Issue with DAX Calculated Percentages Turning to 0s After Sorting Column in Matrix visual

[Richie1602]

Hi everyone,

I'm using Matrix Visual, to show Metric Name ,  amount and custom Percentage( some percentages will come from backend and some from DAX) for multiple accounts. I have to show Metric Names in a specific order which is MetricNo column. If I sort Metric Name by MetricNo, my custom Percentages(Percentages that DAX is calculating) are becoming zero and I am not able to figure out why.

Before Sorting my visuals looks like something similar to this:

After sorting

My DAX is 

Dynamic Metric Measure =
VAR MetricName = SELECTEDVALUE('Sales'[Metric_Name])
VAR TotalSales = CALCULATE(SUM('Sales'[AmountValue]), 'Sales'[Metric_Name] = "Net Sales")
VAR GrossProfit = CALCULATE(SUM('Sales'[AmountValue]), 'Sales'[Metric_Name] = "Gross Profit")
VAR GPDedicated = CALCULATE(SUM('Sales'[AmountValue]), 'Sales'[Metric_Name] = "GP Less Dedicated Op Ex")
VAR OperProfit = CALCULATE(SUM('Sales'[AmountValue]), 'Sales'[Metric_Name] = "Operational Profits")
VAR TotalIncome = CALCULATE(SUM('Sales'[AmountValue]), 'Sales'[Metric_Name] = "Total Income/Loss")

-- Default case for rows: Average of 'Percent' column
VAR DefaultCase = AVERAGE('Sales'[Percent])

-- Subtotal logic: Sum of 'Percent' column
VAR SubtotalCase =
SUMX(
SUMMARIZE(
'Sales',
'Sales'[Metric_Name],
"PercentSum", SUM('Sales'[Percent])
),
[PercentSum]
)

RETURN
IF(
ISINSCOPE('Sales'[Metric_Name]), -- Row-level logic
SWITCH(
TRUE(),
MetricName = "Gross Profit", DIVIDE(GrossProfit, TotalSales, 0),
MetricName = "GP Less Dedicated Op Ex", DIVIDE(GPDedicated, TotalSales, 0),
MetricName = "Operational Profits", DIVIDE(OperProfit, TotalSales, 0),
MetricName = "Total Income/Loss", DIVIDE(TotalIncome, TotalSales, 0),
DefaultCase -- Row-level default
),
SubtotalCase -- Subtotal logic
)

[Kedar_Pande]

@Richie1602 

Revise the DAX Measure:

Dynamic Metric Measure =
VAR MetricName = SELECTEDVALUE('Sales'[Metric_Name])

VAR TotalSales = 
CALCULATE(
SUM('Sales'[AmountValue]),
'Sales'[Metric_Name] = "Net Sales"
)

VAR GrossProfit =
CALCULATE(
SUM('Sales'[AmountValue]),
'Sales'[Metric_Name] = "Gross Profit"
)

VAR GPDedicated =
CALCULATE(
SUM('Sales'[AmountValue]),
'Sales'[Metric_Name] = "GP Less Dedicated Op Ex"
)

VAR OperProfit =
CALCULATE(
SUM('Sales'[AmountValue]),
'Sales'[Metric_Name] = "Operational Profits"
)

VAR TotalIncome =
CALCULATE(
SUM('Sales'[AmountValue]),
'Sales'[Metric_Name] = "Total Income/Loss"
)

VAR DefaultCase = 
AVERAGE('Sales'[Percent])

VAR SubtotalCase =
SUMX(
SUMMARIZE(
'Sales',
'Sales'[Metric_Name],
"PercentSum", SUM('Sales'[Percent])
),
[PercentSum]
)

RETURN
IF(
ISINSCOPE('Sales'[Metric_Name]),
SWITCH(
TRUE(),
MetricName = "Gross Profit", DIVIDE(GrossProfit, TotalSales, 0),
MetricName = "GP Less Dedicated Op Ex", DIVIDE(GPDedicated, TotalSales, 0),
MetricName = "Operational Profits", DIVIDE(OperProfit, TotalSales, 0),
MetricName = "Total Income/Loss", DIVIDE(TotalIncome, TotalSales, 0),
DefaultCase
),
SubtotalCase // For subtotal logic
)

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Anonymous]

Hi @Richie1602 ,

Add the ALL() filter and perhaps you could try the following expression:

Dynamic Metric Measure =
VAR MetricName = SELECTEDVALUE('Sales'[Metric_Name])
VAR TotalSales = CALCULATE(SUM('Sales'[AmountValue]), ALL('Sales'))
VAR GrossProfit = CALCULATE(SUM('Sales'[AmountValue]), 'Sales'[Metric_Name] = "Gross Profit")
VAR GPDedicated = CALCULATE(SUM('Sales'[AmountValue]), 'Sales'[Metric_Name] = "GP Less Dedicated Op Ex")
VAR OperProfit = CALCULATE(SUM('Sales'[AmountValue]), 'Sales'[Metric_Name] = "Operational Profits")
VAR TotalIncome = CALCULATE(SUM('Sales'[AmountValue]), 'Sales'[Metric_Name] = "Total Income/Loss")

-- Default case for rows: Average of 'Percent' column
VAR DefaultCase = AVERAGE('Sales'[Percent])

-- Subtotal logic: Sum of 'Percent' column
VAR SubtotalCase =
SUMX(
SUMMARIZE(
ALL('Sales'),
'Sales'[Metric_Name],
"PercentSum", SUM('Sales'[Percent])
),
[PercentSum]
)

RETURN
IF(
ISINSCOPE('Sales'[Metric_Name]), -- Row-level logic
SWITCH(
TRUE(),
MetricName = "Gross Profit", DIVIDE(GrossProfit, TotalSales, 0),
MetricName = "GP Less Dedicated Op Ex", DIVIDE(GPDedicated, TotalSales, 0),
MetricName = "Operational Profits", DIVIDE(OperProfit, TotalSales, 0),
MetricName = "Total Income/Loss", DIVIDE(TotalIncome, TotalSales, 0),
DefaultCase -- Row-level default
),
SubtotalCase -- Subtotal logic
)

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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