Forum Discussion

Mokegubshook's avatar
Mokegubshook
Icon for Helper I rankHelper I
1 year ago
Solved

Compare number with target incorrect

Hi guys, I have data like in the picture, My problem is I want to compare number of file delivery and Target in everyday. I have many file and each file have the target. My problem is I want number ...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    1 year ago

    Hi,

    I am not sure how your semantic model looks like, but please try something like below whether it works.

    expected result measure: =
SUMX (
    VALUES ( dim_table_name[column_name_in_matrix_visual] ),
    IF ( [CNT_FILE_ID] > [Target], [Target], [CNT_FILE_ID] )
)
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
1 year ago

1. Remember that MIN() and  MAX() can take two parameters.  So MIN([Value],1)  will cap the result at 1 even if [Value] is higher.

2. If you want the totals to work you need to "think like the grand total".  Materialize the results for each day, then do a SUMX over that.