Forum Discussion

lavnyak's avatar
lavnyak
Regular Visitor
2 years ago

Filtered data shows wrong values on measure

Hi Everyone,

I have a table named NDI as below. Created a Measure to derive another table (NDI_DISTINCT) out of it by taking distinct values.

NDI Table:

RGN_CDLINE_OF_BUSINESSSpoken_language_KeyRace_KeyGender_KeyCAR_BUCKETSCH_TELE_CTVIDEO_CTINPRSN_CTURG_CARE_CTSPECIALTY_GRPAge_KeySTART_YEAR_MONTHEND_YEAR_MONTHAverage Membership
 
 
Derived table:
NDI_DISTINCT =
 DISTINCT(
    SUMMARIZE (
        'NDI',
       NDI[Region_KEY],NDI[Line Of Business_KEY],
       NDI[CAR_BUCKET],
       NDI[Spoken Language_KEY],
       NDI[Race_KEY],
       NDI[Gender_KEY],
       NDI[Age],
     "Average Membership",  MAX(NDI[Average Membership])
    )
 
And then I have created a new key column on both tables using the below Measure:
Key =
NDI[Region_KEY]  &
NDI[Line Of Business_KEY] &
NDI[Spoken Language_KEY]  &
NDI[Race_KEY]             &
NDI[Gender_KEY] &
NDI[CAR_BUCKET] &
NDI[Age_KEY]
and then joined those 2 tables.(removed nulls then shows 1 to Many)

userate = volume(coming from NDI) / AVG_MEM(coming from NDI_DISTINCT) * 1000

For AVG_MEM = 
CALCULATE(
SUM(NDI_DIstinct[Average Membership])
,ALLEXCEPT(NDI_DIstinct,NDI_DIstinct[Region],NDI_DIstinct[CAR_BUCKET])
)

It was working fine before the refresh and when I refreshed for the latest quarter it is showing wrong AVG_MEM values when I apply filters like Age, specialty group etc.
Could that be beacause of the Key column that I'm using to relate ? Is there any other way to calculate AVG_MEM instaed of deriving a new table and calculating there?  Any help is appeciated. Thank you!!
amitchandak 
Ashish_Mathur 




Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    lavnyak , Calculated column will become static and will not respond to slicers.
    Either join the new table with dimension that are filtering or try a measure like

     

    For AVG_MEM =
    var NDI_DISTINCT =
    DISTINCT(
    SUMMARIZE (
    'NDI',
    NDI[Region_KEY],NDI[Line Of Business_KEY],
    NDI[CAR_BUCKET],
    NDI[Spoken Language_KEY],
    NDI[Race_KEY],
    NDI[Gender_KEY],
    NDI[Age],
    "Average Membership", MAX(NDI[Average Membership])
    )
    return
    CALCULATE(
    SUMX(NDI_DIstinct, [Average Membership])
    ,ALLEXCEPT('NDI','NDI'[Region],'NDI'[CAR_BUCKET])
    )