Forum Discussion

Vivdroid_C_4222's avatar
Vivdroid_C_4222
Frequent Visitor
1 year ago
Solved

Please help, my Measure shows incorrect value

I have a Date Table and a transaction table called "PWS".There is a unidirectional relationship between date table and PWS (Date table --> PWS)I have created a meaure that tells the total number of outlets based on order net value on an MTD basis.Measure  =
CALCULATE(
    DISTINCTCOUNT(PWS[OutletId]),
    FILTER(
        VALUES(PWS[OutletId]),
        [Order Net Value MTD] = 0
    )
)Order Net Value MTD = CALCULATE(SUM(PWS[OrderNetValue]),DATESMTD(DateTable[Date]))BUT THERE IS AN ISSUE WITH THE ABOVE MEASURE.On 1feb 2025 there are 0 outlets whose order net value  = 0.But, When I select the date as 1 feb 2025 in the date slicer. the above measure shows 59k outlets.AND this is only happening when I filter the order net value = 0. If I set any other number like 100,477, etc it shows the right outlet count.

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    1 year ago

    Vivdroid_C_4222 Would need sample data to confirm but I suspect that perhaps it is counting BLANK as 0. Maybe try == ? Or maybe try this alternative:

    Measure ONV MTD = 
  VAR __End = MAX( 'DateTable'[Date] )
  VAR __Start = EOMONTH( __End, -1 ) + 1
  VAR __Table = FILTER( 'PWS', [Date] >= __Start && [Date] <= __End )
  VAR __Result = SUMX( __Table, [OrderNetValue] )
RETURN
  __Result

Measure =
  VAR __Table = 
    DISTINCT( 
      SELECTCOLUMNS( 
        FILTER( 'PWS', [Order Net Value MTD] = 0 ),
        "__ID", [OutletID]
      )
    )
  VAR __Result = COUNTROWS( __Table )
RETURN
  __Result


CALCULATE(
    DISTINCTCOUNT(PWS[OutletId]),
    FILTER(
        VALUES(PWS[OutletId]),
        [Order Net Value MTD] = 0
    )

     

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    1 year ago

    Vivdroid_C_4222 Would need sample data to confirm but I suspect that perhaps it is counting BLANK as 0. Maybe try == ? Or maybe try this alternative:

    Measure ONV MTD = 
  VAR __End = MAX( 'DateTable'[Date] )
  VAR __Start = EOMONTH( __End, -1 ) + 1
  VAR __Table = FILTER( 'PWS', [Date] >= __Start && [Date] <= __End )
  VAR __Result = SUMX( __Table, [OrderNetValue] )
RETURN
  __Result

Measure =
  VAR __Table = 
    DISTINCT( 
      SELECTCOLUMNS( 
        FILTER( 'PWS', [Order Net Value MTD] = 0 ),
        "__ID", [OutletID]
      )
    )
  VAR __Result = COUNTROWS( __Table )
RETURN
  __Result


CALCULATE(
    DISTINCTCOUNT(PWS[OutletId]),
    FILTER(
        VALUES(PWS[OutletId]),
        [Order Net Value MTD] = 0
    )

     

  • v-sathmakuri's avatar
    v-sathmakuri
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi  Vivdroid_C_4222 ,

    May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

    Thanks!!