Forum Discussion

lcerrapa's avatar
lcerrapa
Icon for Helper I rankHelper I
7 years ago
Solved

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, s...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    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)])

     

    Best regards,

    Yuliana Gu

     

  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    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

v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

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)])

 

Best regards,

Yuliana Gu

 

lcerrapa's avatar
lcerrapa
Icon for Helper I rankHelper I
7 years ago

Many thanks for your time v-yulgu-msft

 

It doesn't work for me :smileysad:

 

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:

 

 

 

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:

 

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:

 

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!

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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