Forum Discussion
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 of file delivery not more then target (If it more than target set it as target) like in Dax picture. Does it have anyway to done this?
By the way I want measure to return like column K for further calculation.
My data looklike:
Dax
In table I show, the raw data is correct base on condition from dax I provide. But total is wrong (The red box), It should sum with Var D condition but looklike it sum with Var A.
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] ) )
3 Replies
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.
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] ) )Thank you so much, It working!
