topic Re: Wrong measurement with IF in DAX Commands and Tips

Wrong measurement with IF

Newbobotime — Mon, 15 Apr 2024 09:06:33 GMT

Hi everyone,
I have a problem, maybe it's simple but I don't understand how to solve it.

I present the data for this measure in a table in which I would like to calculate the percentage of a hypothetical number of completed orders.
For some strange reason completed orders always seem to be higher than total orders so I thought I'd put an IF; if the result of the completed orders count is greater than the total orders count, do the total orders count, otherwise do the completed orders count.
If I look at the data presented for each record it seems correct but the total sum does not add up, it does not remove the excess numbers.

total_order = VAR new = CALCULATE(COUNTX(order_new, order_new [code])) VAR complete = CALCULATE(COUNTX(order_complete, order_complete[code])) VAR result = IF(new > complete, new, complete) RETURN result

Re: Wrong measurement with IF

Pavel_Gutsol — Mon, 15 Apr 2024 09:57:40 GMT

Please, try this version:

total_order =
VAR new =
CALCULATE ( COUNTX ( VALUES ( order_new[code] ), order_new[code] ) )
VAR complete =
CALCULATE ( COUNTX ( VALUES ( order_complete[code] ), order_complete[code] ) )
VAR result =
IF ( new > complete, new, complete )
RETURN
result

Re: Wrong measurement with IF

Newbobotime — Mon, 15 Apr 2024 13:22:06 GMT

Thanks Pavel, for the help but it doesn't work,
I assume this way it can't iterate, it proposes the same number for all records 🙄

Re: Wrong measurement with IF

Anonymous — Tue, 16 Apr 2024 03:18:20 GMT

Thanks Pavel_Gutsol

Hi, Newbobotime

Can you make some sample data for your problem, and what results do you expect? What fields do you use this metric in your table visual, and what are the contexts it depends on? It would be helpful to have some explanation of the sample data you provided that you expect the results to be. When providing sample data, please do not include any privacy data. Here's a sample I created:

order_new table:

order_complete table:

I use the following DAX expression to find the percentage of completion to new:

Percentage = VAR _all_new = CALCULATE ( COUNTAX ( 'order_new', 'order_new'[code] ), ALL ( order_new ) ) VAR _selected_code = SELECTEDVALUE ( order_complete[code] ) VAR _complete = CALCULATE ( COUNTAX ( FILTER ( ALL ( 'order_complete' ), 'order_complete'[code] <= _selected_code ), 'order_complete'[code] ) ) RETURN DIVIDE ( _complete, _all_new )

Also, you mentioned that in your table visual, total doesn't work, I used the DAX formula you provided here and created a total measure:

Total = SUMX('order_complete',[total_order])

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Wrong measurement with IF

Newbobotime — Tue, 16 Apr 2024 07:19:58 GMT

Re: Wrong measurement with IF

Newbobotime — Tue, 16 Apr 2024 07:27:58 GMT

I would like to get an output like this, where however the total number of completed orders can only be equal to or less than the orders.

I take data from two tables orders and completed orders, I don't want to identify duplicate data, I just want to count orders by day and location for the two tables and get a maximum of 100%

As I wrote at the beginning I thought of simply solving with an IF, but that's not possible.

I assume I need to create a virtual table with both data; orders and completed orders, and then set an IF condition.

Re: Wrong measurement with IF

Newbobotime — Tue, 16 Apr 2024 07:36:55 GMT

Re: Wrong measurement with IF

Newbobotime — Tue, 16 Apr 2024 07:37:05 GMT

with a simple "IF", I solve the percentage problem for each record but the subtotal is wrong

Re: Wrong measurement with IF

Anonymous — Tue, 16 Apr 2024 08:04:51 GMT

Hi, Newbobotime

Thank you very much for your reply. You can try sumx to get the correct total, and your new Total Orders completed_IF measure might look like this:

Total_Orders_Completed_IF = SUMX ( 'LOCATION_CODE', [Total Orders completed_IF] )

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Wrong measurement with IF

Newbobotime — Tue, 16 Apr 2024 10:02:40 GMT

Maybe I solved it.
Sure the code could be written better but it seems to work.
First of all I created a new table with summarize, determining the total orders and for the completed orders data I performed a comparison with the IF.
Subsequently I created the various measurements using this table

Re: Wrong measurement with IF

Anonymous — Wed, 17 Apr 2024 01:25:23 GMT

Hi, Newbobotime

Thank you very much for your reply and the solution you shared. I decided it was a great idea to create a summary table. You can mark your response as a solution so that others in the community can quickly find a solution if they encounter a similar problem. Thanks again for sharing!

Best Regards

Jianpeng Li

Re: Wrong measurement with IF

Newbobotime — Wed, 17 Apr 2024 07:34:58 GMT

Perhaps there is a more correct method but I think I solved it by creating a table with "Summarize" and then setting the various measures; total orders, total_completed_orders, %completed_orders