Forum Discussion

Prima_'s avatar
Prima_
Resolver I
3 years ago

HardCoded value works

Hi,

If I hardcode the value of the Product, in the Column, it calculates the Sum the Sales by Product correctly.

 

TotalYTD = TOTALYTD(SUM('Table'[Sales]),'CALENDAR_Table'[Date],filter('Table','Table'[Product]="BN"))

But, if I try to use selected value from the Product filter instead, it doesn't group by product... it sums the sales of all products monthwise, which is wrong. 

TotalYTD = TOTALYTD(SUM('Table'[Sales]),'CALENDAR_Table'[Date],filter('Table','Table'[Product]=[Measure]))

Measure= SELECTEDVALUE(Table[Product])

 

 

If I use the filter expression directly, it doesn't compute the Sum of Sales at all.

TotalYTD = TOTALYTD(SUM('Table'[Sales]),'CALENDAR_Table'[Date],filter('Table','Table'[Product]=SELECTEDVALUE(Table[Product])))

 

Any help is appreciated.

 

Thanks.

 

 

 

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    HI Prima_,

    You can try to use the following measure formula if it suitable for your requirement:

    YTD =
VAR currdate =
    MAX ( 'CALENDAR_Table'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            YEAR ( Table[Date] ) = YEAR ( currdate )
                && Table[Date] <= currdate
        ),
        VALUES ( Table[Product] )
    )

    Regards,
    Xiaoxin Sheng

    • Prima_'s avatar
      Prima_
      Resolver I
      3 years ago

      Thank you. 

      This measure returns the same value (sum of sales for all months for the selected product) for all the months. For the data shown below, it returns 4039 as the value for all months.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi Prima_,

        Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

        How to Get Your Question Answered Quickly  

        Regards,

        Xiaoxin Sheng