Table Totals Wrong for Measure

[jptak]

I have burned a ton of hours reasearching and testing posted solutions and cannot seem to get the total on a measure for a Table visualization to come out right. The detail row values are exactly right.

Conceptually, I understand that the Total row in the table is missing the filter context applied at the row level.

I found the solution at https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907 and tried that approach as I will detail below but I just can't get it right.

My visualization is shown below:

The donut cross filters the page by down machines in 2 statuses (Down for < 20 days or Down for > 20 days). The bar chart shows the machines that are down by category. Clicking a donut segment or bar in the chart cross filters the details in the table to show only machines that fall into the selected status or category.

As you can see, the total above should be $24.44.

Here's the DAX measure for that column:

Test = 

Var RowValue = 0 + ([Days Down Count] * SUM('Dim Equipment (Down)'[DailyRevenue]))

Var __Table = SUMMARIZE('Dim Equipment (Down)','Dim Equipment (Down)'[Asset Number], "Total Lost Rev", RowValue)

RETURN

IF(

 HASONEFILTER('Dim Equipment (Down)'[Asset Number]), 

 RowValue,

 SUMX(__Table, [Total Lost Rev])

)

I know enough DAX to be dangerous. But this one has me stumped.

I took out all the other columns from the table leaving only the two above to try to get this to work. The other columns all come from the same dimension table the is related to the fact table which hosts the measure.

There are other filters contexts in play as well if that matters. There is a slicer that is synced to this page for a location attribute as well as a relative date Page Filter that only considers the previous day.

Any help and explanation as to why the solution above doesn't work like the liked post states it should would really be appreciated. I read nearly every post on this topic on this forum as well as read and re-read the doc on SUMMARIZE...but it just isn't clicking.

Thank you in advance!

[parry2k]

@jptak try this, I broke it down to two measures

Row Revenue = 0 + ([Days Down Count] * SUM('Dim Equipment (Down)'[DailyRevenue])

Test = 
IF(
HASONEFILTER('Dim Equipment (Down)'[Asset Number]), 
[Row Revenue],
 SUMX(VALUES('Dim Equipment (Down)'[Asset Number]), [Row Revenue] )
)

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[jptak]

Thanks for the reply. That didn't work. It definitely changed the sum, but it still isn't correct.

Would it help if I posted the DAX for that table captured by the performance analyzer. If so, here it is:

// DAX Query
DEFINE
VAR __DS0FilterTable = 
TREATAS({"TRASH PUMP GAS 2"" - 4"""}, 'Dim Equipment (Down)'[Cat Class Category])

VAR __DS0FilterTable2 = 
TREATAS({"Down < 20 Days",
"Down > 20 Days"}, 'Dim Equipment (Down)'[Status])

VAR __DS0FilterTable3 = 
FILTER(
KEEPFILTERS(VALUES('Dim Date'[CalendarDate])),
AND(
'Dim Date'[CalendarDate] >= DATE(2020, 5, 3),
'Dim Date'[CalendarDate] < DATE(2020, 5, 4)
)
)

VAR __DS0FilterTable4 = 
TREATAS(
{("GENERAL TOOL", "EAST TERRITORY", "REGION 03", "CENTRAL DISTRICT", "CHARLOTTE NORTH PC582")},
'Dim Branch'[Specialty Name],
'Dim Branch'[Territory Name],
'Dim Branch'[Region Name],
'Dim Branch'[District Name],
'Dim Branch'[Branch Name]
)

VAR __ValueFilterDM1 = 
FILTER(
KEEPFILTERS(
SUMMARIZECOLUMNS(
'Dim Equipment (Down)'[Asset Number],
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
__DS0FilterTable4,
"Test", 'Fact Daily Inventory'[Test],
"Days_Down_Count", IGNORE('Fact Daily Inventory'[Days Down Count])
)
),
[Days_Down_Count] > 0
)

EVALUATE
TOPN(
502,
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL('Dim Equipment (Down)'[Asset Number], "IsGrandTotalRowTotal"),
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
__DS0FilterTable4,
__ValueFilterDM1,
"Test", 'Fact Daily Inventory'[Test]
),
[IsGrandTotalRowTotal],
0,
'Dim Equipment (Down)'[Asset Number],
1
)

ORDER BY
[IsGrandTotalRowTotal] DESC, 'Dim Equipment (Down)'[Asset Number]

[jptak · Accepted solution]

I found another way to solve this problem by bringing the daily revenue number into the Fact table then doing a simple CALCULATE function to ignore the date slicer on the page as:

Lost Revenue = 0 + CALCULATE(SUM('Fact Daily Inventory'[Daily Revenue]), 'Fact Daily Inventory'[EquipmentStatusCode] IN {"D", "N"}, ALL('Dim Date'[CalendarDate]))

Once I did this, the table computed the totals correctly.