Displaying Sequential Months with multiples years

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.

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)

Following is result of above measure:

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.

@benwann1,

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.

  • Brian
1 Like

@benwann1,

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:

  1. Use TREATAS to get rid of the two bridge tables. Will make your DAX slightly more complicated, but IMO that is more than offset by the simplicity gains in the data model.
  1. Display the model in Collie format, with the dimension tables all at the top level, so visually all the relationships/filters flow “downhill”:

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

  • Brian
2 Likes

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

@benwann1,

Haven’t forgotten about you here. Still working on cracking this one…

  • Brian
1 Like