Hi,
I am interested to understand how to display a subtotal in every row of my table.
For this calculation, I need to create a ratio and the blanks make this not possible.
Any ideas and thoughts on the formula and process to use. I need to do the same thing for the sum of CY & PY lbs.
https://drive.google.com/file/d/1mqMTXQeiGZVVNKomQVKrMK-L6LKyhFIp/view?usp=sharing
Hi @benwann1,
You can try below formula and it should work. I should checked if it shows blank, it should change it to 0.
summarize all cy =
VAR myMeasure = sumx(SUMMARIZE(âAll data newâ,âAll data newâ[Component Material], âAll data newâ[Component Description], âAll data newâ[Material]),[CY FG all])
RETURN
IF(ISBLANK(myMeasure),0,myMeasure)
I should have been more specific. I want to return the same result for where there is a blank or 0. For every material/component combination, I need to display the total finished goods produced. 2700 needs to appear on every line of that column.
I canât use allselected() because that only works when an individual material/component combination is filtered on. I need something that sticks when all materials are selected and displays the correct totals.
Any chance that youâve been able to take a look at this puzzle? No one that Iâve talked to seems able to solve it. I believe that it is an issue where I need to overcome some filters.
https://1drv.ms/u/s!AgtncqXTJUyV-k4e2KoNJ01D3U3Q?e=B8ju5b
I need the same subtotal of the FGâs to copy down for CY and PY for each FG. When I filter on an individual material, the calculation is perfect. When I unfilter, itâs garbage. What am I missing?
Display of what I need to achieve when filtered on a specific FG and unfilter to see all FGâs.
I havenât had a chance to yet. Been working on a couple of extended support requests this week, and owe @SamSPAIN a response first, but will take a look at it as soon as Iâve finished that one.
Iâm digging into this one now, but first wanted to give a big thumbs up to the great work youâve done reworking your data model. When we worked on the totals/filter conditions issues back in Dec, this model was chockablock with inactive and bidirectional relationships, making it pretty hard to follow. If you donât mind, I wanted to offer two suggestions, which I think will simplify/clarify it even further:
@sam.mckay promotes this as a best practice, and I completely agree that it makes any data model clearer and more intuitive (I find it also really helps me better visualize what DAX is doing).
Will get back to you later today on the issue at hand, but just wanted to let you know that I think the overall model is looking good.
Thank you, @BrianJ. I will look to make those suggested changes to the model now.
I look forward hearing to hearing your further thoughts later.
Ben
Havenât forgotten about you here. Still working on cracking this oneâŚ
UPDATE 2/18: still working this. Breaking some of the larger measures down into components to try to isolate the problem, and rewriting some of the more complex nested ifs as SWITCH statements to test /confirm the logic. Welcome any other forum membersâ eyes on this one as wellâŚ
I agree with you that this is likely a filter override issue, but have been unable to pinpoint the source of the problem, even after wrestling with it over multiple nights . I have called for reinforcements - sending this back to the entire expert team. Hopefully, more eyes on the problem will identify a solution. Sorry for the delay.
Thank you for continuing to explore this one, @BrianJ. I appreciate all of your help so far and commitment to getting to the bottom of this analysis. Once itâs done, this will make a shareable case-study, hopefully.
Ben
Good news - I kicked it to the expert team and it looks like @Melissa nailed it. Hereâs her code:
CY FG all _V2 =
VAR vTable =
SELECTCOLUMNS (
ALLSELECTED ( 'All data new' ),
"Plant", [Plant],
"Material", [Material],
"Date", [Date],
"Total FG Lbs", [Total FG Lbs]
)
VAR Result =
SUMX (
SELECTCOLUMNS (
SUMMARIZE (
vTable,
[Material],
[Plant],
[Date],
[Total FG Lbs]
),
"Material", [Material],
"Value", [Total FG Lbs]
),
MAXX (
vTable,
[Value]
)
)
RETURN
Result
I copied it into your most recent PBIX and it looks like it returns the proper values regardless of what is selected in the month slicer (or no selection at all).
Thanks, @Melissa - great work!
Thank you, @BrianJ. For individual materials that I filter on, I see that it works great. However, when I unfilter to show all materials for the plant, the subtotal is by cumulative finished goods for the whole plant, instead of the FG subtotal, sku by sku. Any thoughts on how to control this?
Hi @benwann1,
Iâm hoping you can save me a lot of time because I might be missing some vital context here⌠Could you explain your requirement again in more detail by supplying some concrete examples of your filter settings and the expected results?
That will be a big help in working through this issue. Thanks in advance.
Hi @Melissa,
The Excel file that this process is replicating is here:
https://drive.google.com/open?id=1YCEQxXttpflqFTYg7CTvjJKBLNiZ36iS
In short summary, we produce many finished goods (FG) at each plant. Each finished good consumes various resources (components). We need to compare how much of each component was consumed to produce FGs this year and last year for the same time period.
The trick is, though, that for some components, we have substitutions yoy and the same material sku wonât be used but a similar sku in the same classifcation will be.
The calculations in the workbook is to divide the Cy component lbs over the CY FG lbs. That gives us a ratio of usage. We then divide PY component lbs by the PY FG lbs. Next, we multiply the PY usage rate by the CY FG lbs. That is why having the FG amount for both years is so critical to this analysis functioning properly.
We then need this analysis to show the results correctly for both individual SKUs and all SKUs when the filter context is changed.
Does that help? Ben
Due to the length by which this post has been active, we are tagging it as Solved. For further questions related to this post, please make a new thread. You may check this how-to guide for reference - How To Use The Enterprise DNA Support Forum