Calculate at the lowest level and then sum

DAX DAX Calculations
1

I have a model with probably about 10 fact tables. They are all at the same granularity
Product, Outlet, Fiscal Period

My issue is that demand units , inventory units, Product price, expiring stock units are all on seperate fact tables
I have created measures called
[demand units] , [inventory units], [Product price], [expiring stock units]

My measure for Overstock value is
if [demand units] = 0
then ([Invetory units] * [Product Price]) - ([expiring stock units] * [Product Price])
else ([Invetory units] * [Product Price]) - ([expiring stock units] * [Product Price]) - ([demand units] * [Product Price])

When displayed at the lowest level in the Product hierarchy this works as expected

However at any level above the base product level when it does the calculation totals , subtotals do not match because it is not the sum of the lower level.

Is there a way of forcing this to calculate at the lower level and then Sum that to the levels above.
The only way I have seen of this being done is by using SELECTCOLUMNS and then unioning all the fact tables .

Looking for a best practice for this sort of scenario
Thanks
E

2

I have attached an example.
The measure Forecast Units =
If ( ([Demand Units] = 0 || [Demand Value] = BLANK()) ,
BLANK()
,[Demand Units]-[Inventory Units]
)

What I am hoping to do here is to calculate this at product level before it is rolled up. I dont want to put the demand into another table. It needs to be done in DAX.

My current report has many more fact tables and many more measures that have the same problem. I am looking for something like SUMX and related but without linking all the tables to each other as that will get messy.

Hope this makes sense. It has been driving me niuts
E

Calculating at a lower level.pbix (70.1 KB)

3

Bumping this post

4

Hi @ells! We noticed that your inquiry was left unsolved for quite some time now.

Looks like your inquiry was out of the experts and users’ bounds.

We strive to answer ALL inquiries in the forum. However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!

5

Removing Totals and Subtotals

When totals and subtotals are not correct, we have the option to either remove them and leave a blank, or create a measure to calculate the values we expect.

Step 1 – Create a logical test to see if a row is a total or subtotal

Each one of the following functions will return a true/false. HASONEVALUE, ISFILTERED and HASONEFILTER.

We need to test to see if there is a filter on the product, as it is the product field that is used in the table.

=HASONEVALUE(Products[Product]) – This tests to see if the products column has a value. In the case of the totals and subtotals, there is no filter on products, so a false is returned.
The filter is on the year

=HASONEFILTER(Products[Product]) – This tests to see if the products column has one filter. In the case of the totals and subtotals, there is no filter on products, so a false is returned. The filter is on the year.

=ISFILTERD(Products[Product]) – This tests to see if the products column has any filter on the products column. In the case of the totals and subtotals, there is no filter on products, so a false is returned.

Step 2 – Use Logical test in an IF statement to apply values to non-total rows

Using IF statements, we can now replace these true with the expression No of days with sales.

=if(HASONEVALUE(Products[Product]),[No of days with sales],0)

=if(HASONEFILTER(Products[Product]),[No of days with sales],0)

=if(ISFILTERED(Products[Product]),[No of days with sales],0)

6

@adesinamk
Many thanks for the response. What I did not say is that the users can personalise the visual.

The only solution I could find is as below. My issue is a litlle more complex than the PBI I uploaded.

The issue is I need to create the logic for Measure X: if [Meausre A] is >0 Then [Meausre B] -[Meausre C] - [Meausre D]

Definition for Measure B is
[Messure B] = SUMX(‘TableA’,‘TableA’[Column1] *[Column2])

So Measure X becomes
[Messure X] =
VAR NumberOfRows = SUMX(‘TableA’,1)
RETURN
SUMX(
‘TableA’,
if (
[Meausre A] is >0 ,
‘TableA’[Column1] *[Column2]) - (DIVIDE([Meausre C] + [Meausre D] ,NumberOfRows)),
blank()
)
)

Using the SumX to iterate through row at a time means that this will work at all levels. I had to add a check for the number of Rows as this would not be guaranteed to be one.

This is not a neat solution but it does seem to work.

I need formulas that are robust and as I have personalise visuals enabled I dont think I could have used HASONEVALUE, ISFILTERED and HASONEFILTER

Thanks
E