cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Table Totals Wrong for Measure

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:

DownAssetIssue.png

 

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!

 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper I
Helper I

Re: Table Totals Wrong for Measure

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.

View solution in original post

3 REPLIES 3
Highlighted
Super User IV
Super User IV

Re: Table Totals Wrong for Measure

@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!

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Helper I
Helper I

Re: Table Totals Wrong for Measure

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]
Highlighted
Helper I
Helper I

Re: Table Totals Wrong for Measure

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.

View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors