cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
lcerrapa Regular Visitor
Regular Visitor

Another question on totals

Hello.

 

I found a lot of similar examples but nothing fits what I'm looking for. I hope you can help me. It's a very simple example. I have annotations of stock variations only (store, material, stock available). I need the stock available for a selected date on each store. Example: I have this FactTable and I select the following date: 14/04/2018

 

example.jpg

I defined a measure (wrong):

Stock (t) =
VAR startDate = CALCULATE(MIN('Calendar'[Date])) <-- Selected Date from a calendar, it may be only a month selection
VAR searchDate = CALCULATE(MAX(FactTable[Date]); ALL('Calendar'); FactTable[Date]<startDate) 

RETURN CALCULATE(SUM(FactTable[Quantity]); ALL('Calendar'); FactTable[Date] = searchDate)

 

I know the total I get is correct as it is defined but I'm a little blind and I don't know how to group the material and the store and get the right grand total. I get the total for the max date of the material, not material and store.

 

Thanks in advance

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: Another question on totals

Hi @lcerrapa,

 

Please modify the formula for [Stock (t)] and create an extra measure based on [Stock(t)].

Stock (t) =
VAR startDate =
    CALCULATE ( MIN ( 'calendar table'[Date] ) )
VAR searchDate =
    CALCULATE (
        MAX ( FactTable[Date] ),
        FILTER (
            ALLEXCEPT ( FactTable, FactTable[Material], FactTable[Store] ),
            FactTable[Date] < startDate
        )
    )
RETURN
    CALCULATE (
        SUM ( FactTable[Quantity] ),
        ALL ( 'calendar table' ),
        FactTable[Date] = searchDate
    )

stock total = SUMX(VALUES(FactTable),[Stock (t)])

1.PNG

 

Best regards,

Yuliana Gu

 

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: Another question on totals

Hi @lcerrapa ,

 


If I remove this relationship I get the totals you told me. I have two questions about this:

1.- Is possible to get the totals with this relationship?

2.- From design point of view, I always saw this relationship active. Is a better solution not to have this relationship in some cases?


No. In that case, you have to remove this relationship. If there existing a relationship, it would filter out those records from fact data table where dates aren't matched with the selected date in slicer. That makes it unable to fetch the corresponding values, whose corresponding dates are before the selected date. 

 

In short, in your scenario, removing this relationship makes sense.

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Community Support Team
Community Support Team

Re: Another question on totals

Hi @lcerrapa,

 

Please modify the formula for [Stock (t)] and create an extra measure based on [Stock(t)].

Stock (t) =
VAR startDate =
    CALCULATE ( MIN ( 'calendar table'[Date] ) )
VAR searchDate =
    CALCULATE (
        MAX ( FactTable[Date] ),
        FILTER (
            ALLEXCEPT ( FactTable, FactTable[Material], FactTable[Store] ),
            FactTable[Date] < startDate
        )
    )
RETURN
    CALCULATE (
        SUM ( FactTable[Quantity] ),
        ALL ( 'calendar table' ),
        FactTable[Date] = searchDate
    )

stock total = SUMX(VALUES(FactTable),[Stock (t)])

1.PNG

 

Best regards,

Yuliana Gu

 

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
lcerrapa Regular Visitor
Regular Visitor

Re: Another question on totals

Many thanks for your time @v-yulgu-msft

 

It doesn't work for me Smiley Sad

 

I defined the measure as you told me (Stock (t) and stock total). I added two more intermediate measures to check the dates in the Stock(t) measure, and both are correct.

I defined another measure combining both measures:

 

Total = IF(HASONEVALUE(FactTable[Store]); [Stock (t)]; [stock total])
 
And the last measure I think is the key to see the behavior:
_HasOneValue_FactTable_Store = HASONEVALUE(FactTable[Store])

 

Here is the output I get:

 

Captura1.JPG

 

 

If there is no data for the selected date, the total measure doesn't show anything. In other way, if I select a date with information, the totals shows data but only but that date, not the grand total:

Captura2.JPG

 

It only has value on the store 1003, not on 1002.

 

Could you help me wiht this issue?

 

Thanks!

 

I edit the reply. 

The difference is because I have a relationship between both tables:

Captura3.JPG

 

If I remove this relationship I get the totals you told me. I have two questions about this:

1.- Is possible to get the totals with this relationship?

2.- From design point of view, I always saw this relationship active. Is a better solution not to have this relationship in some cases?

 

Thanks!

Community Support Team
Community Support Team

Re: Another question on totals

Hi @lcerrapa ,

 


If I remove this relationship I get the totals you told me. I have two questions about this:

1.- Is possible to get the totals with this relationship?

2.- From design point of view, I always saw this relationship active. Is a better solution not to have this relationship in some cases?


No. In that case, you have to remove this relationship. If there existing a relationship, it would filter out those records from fact data table where dates aren't matched with the selected date in slicer. That makes it unable to fetch the corresponding values, whose corresponding dates are before the selected date. 

 

In short, in your scenario, removing this relationship makes sense.

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
lcerrapa Regular Visitor
Regular Visitor

Re: Another question on totals

Thanks a lot, @v-yulgu-msft