topic Re: Issue with DAX Calculated Percentages Turning to 0s After Sorting Column in Matrix visual in DAX Commands and Tips

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

Richie1602 — Wed, 18 Dec 2024 20:35:14 GMT

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
)

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

Anonymous — Thu, 19 Dec 2024 08:08:56 GMT

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.

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

Kedar_Pande — Thu, 19 Dec 2024 08:22:07 GMT

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