Alternative to ALLSELECTED to get Grand Total of table variable

DAX DAX Calculations
1

I want to get a cumulative total from a variable table but using the code below limits my use because of allselected. The moment I add more attributes to the table, the cumulative total changes but I don’t want it to do that.

I have the following data tables:

Dim Date:

  • Calendar date (key)
  • FY

Dim Product:

  • Order # (key)
  • Item #
  • Brand (one brand could have several item #s under it)

Fact Sales:

  • Calendar date (key)
  • Order # (key)
  • Units
  • Sales

I also have the following measures:
FYTD Sales - Gives me the 2018 fiscal year to date sales of the max date selected
PYTD Sales - Gives me the corresponding prior year to date sales
FYTD ASP - Gives me the 2018 average selling price (Sales / Units). If units is 0 or sales is -ve (due to a credit), ASP will be zero.
PYTD ASP - Gives me the corresponding period average selling price (Sales / Units). If units is 0 or sales is -ve (due to a credit), ASP will be zero.

Issue:
I have a table with rows that shows the item#. My goal is to get % of sales (sales / Total sales) for each item# whose FYTD_ASP and PYTD_ASP <> 0. This measure would be shown on the table. The code below is what I have so far.

While the code works on item#, the moment I add another column (such as brand) with my item# still in the first column of the table, the ALLSELECTED messes up the FYTD_TotSales by taking the cumulative brand total sales for a group of items within that brand. I don’t want it to do that. I just want it to always take the total of all items# sales and not segregate it by brand.

PercentSalesofVirtualtable =
//1. Create a filtered table of products without all the new, discontinued and credit/other products (the FYTD_ASP and PYTD_ASP <>0, filters for these products)

Var FilteredItemTable = FILTER(ALLSELECTED(‘Dim-Product’[Item#]), [FYTD_ASP$]<>0 && [PYTD_ASP]<>0)

//2. Compute FYTD Total Sales from filtered table required
Var FYTD_TotSales = CALCULATE([FYTD_Sales],FilteredItemTable)

//3. Compute % of Sales
Return SUMX(ADDCOLUMNS(VALUES(‘Dim-Product’[Item#]), “SalesPecent”,
IF( ISBLANK([PYTD_ASP]) || ISBLANK([FYTD_ASP]), 0, [FYTD_Sales] / FYTD_TotSales)),
[SalesPercent])

2

Any chance I can actually see the results.

It a little difficult to understand all the behaviour of these formulas without seeing all the results laid out in a table.

Thanks

3

Thanks for the quick response. Let me start with the basic question and I’ll attempt to ask the question a different way just because I can see how can get lost with the formula above (some repeated info from above).

I have the following data tables:

Dim Date:

  • Calendar date (key)
  • FY

Dim Product:

  • Item # (key)
  • Brand (one brand could have several item #s under it)

Dim Customer:

  • ShipTo# (key) unique customers

Fact Sales:

  • Calendar date
  • Item#
  • ShipTo#
  • Units
  • Sales

I also have the following measures:
FYTD Units - Gives me the 2018 fiscal year to date units of the max date selected
PYTD Units - Gives me the corresponding prior year to date units
FYTD ASP - Gives me the 2018 average selling price (Sales / Units). If units is 0 or sales is -ve (due to a credit), ASP will be zero.
PYTD ASP - Gives me the corresponding period average selling price (Sales / Units). If units is 0 or sales is -ve (due to a credit), ASP will be zero

Problem:
I want to determine the total FYTD units for a filtered sales table at a transaction (shipTo#) level. The filtered table should filter the distinct shipTo# for FYTD_ASP and PYTD_ASP when they are greater than zero. Finally, the total should be dynamic based on any slicer in the Dim-Product Table.

Here is my code:

FYTDTotUnits = 
Var FilterTable = FILTER(VALUES('Fact-Sales'[Shipto#]), [PYTD_ASP]<>0 && [FYTD_ASP$]<>0)
Return CALCULATE([FYTD_Units],FilterTable, ALLSELECTED('Dim-Product'))

Below is the result:
There is a date slicer for FY 2018 and a brand is selected. The output should show 800 on every single line, but instead it shows some weird numbers and a weird total for the measure as well. (the picture is just a small extract of my entire table).

I manually computed 800 by filtering my data in excel for FYTD_ASP<>0 and PYTD_ASP<>0 for each shipTo#. The table was filtered for 2018 and a brand was selected.

Capture

4

Sorry for delay here, been in transit. Will replay as soon as I have time to review in more detail

5

Thanks Sam!

6

This is a tough one without seeing everything and testing a few things.

I’m pretty confident that the below part of the formula is the issue

FILTER(VALUES('Fact-Sales'[Shipto#]), [PYTD_ASP]<>0 && [FYTD_ASP$]<>0)

And it’s likely not working as you would expect.

Also I’m not sure you need this part of the formula either

CALCULATE([FYTD_Units],FilterTable, ALLSELECTED(‘Dim-Product’))

Because isn’t each item part of the products table? So for each different row in the results you are just getting that one product. I just don’t think you need this just from looking at it. Having this here certainly complicates the solution so I would avoid ALLSELECTED if you can.

So just thinking aloud here, you want to work out the FYTD in units of each product if they have sales previous sales.

Let’s just walk through what you have here. I’ve looked to simplify it. I don’t really have a proper solution as finding it hard to imagine every moving part here without being to test it.

Hopefully this helps walking through the logic

So this below is maybe all you need?

CALCULATE([FYTD_Units],
FILTER(VALUES('Fact-Sales'[Shipto#]), [PYTD_ASP]<>0 && [FYTD_ASP$]<>0)

This formula is working like this.

For every row in the results table, you first are filter by each product product item.

Then the VALUES part of the equation is then iterating through every single ship to location and remaining ‘in’ if the PYTD and FYTD are anything above zero.

Then you have a remaining context of just those ship to regions that evaluated to TRUE and then calculated the total FYTD units.

Hopefully this is the logic you are looking for.

Past this my recommendation is to really think deeply about what each part of the formula is doing in the current context. You may even need to test the behaviour of the PYTD and FYTD calculations at every part of the evaluation.

From here if you still need assistance with this, I would have to see all the formulas and likely the model.

As I say there are many moving parts with this one. It’s not a super easy one, as you know.

Chrs

7

Thanks! The formula you suggested gives me FYTD units for each shipTo#. What I’m looking for is to return the grand total of that table on each row. I want to use this grand total to do some % of total for each shipTo# where PYTD_ASP and FYTD_ASP <> 0. This is why I was using ALLSELECTED. Could you suggest another formula to get the grand total of the filtered table?

8

I’m going to have to see a model I think. I’ve been reviewing this many times and feeling I’m just stabbing in the dark unless I can see and test it.

Should be able to solve it relatively quickly if I can see a model example of all this together.

Thanks