Create a new column based on distinct result

Question

Hi,&nbsp;Good day.&nbsp;I need some help to get an accurate audit result from a given audited data. But first, let me share to your our audit process and give you samples. See details as follows:&nbsp;1. We extracted on our system the new set of Unaudited Data for the month. And it looks like this:&nbsp;2. On or before the given deadline, auditors must finish and submit their audit works based on the extracted audit data above. Like this:&nbsp;3. Once submitted, we, reporting team, must provide column for audit result. But we always do it manually. Until Power BI came up, we need to transform our reports to Power BI. The result of another column showed like this:&nbsp;You may also notice that Audit Result - Amounts may vary and depends on the auditor. Now, for this particular column shown above (Audit Result - Amount in Question), we do have problem creating it due to no idea what DAX or Formula I must use. Kindly help us for this.&nbsp;Should you have any questions, please do let us know.&nbsp;Thanks and stay safe,Echo

jimmy801 · Answer

Hello Anonymous&nbsp;
&nbsp;
I don't know how your last table is calculated. It shows 1200 for first Audit, but in your table you have 4 lines with 1200, 1200, 1000 and 1200. Is this a MAX-calculation?
&nbsp;
BR
&nbsp;
Jimmy

anonymous · Answer

Hi Jimmy801&nbsp;,&nbsp;Good day.&nbsp;I cant say if this is a MAX-Calculation since I dont have any idea. But the amounts indicated there are distinct and might be based on the document submitted. For example:&nbsp;1200 is the total amount of all the BULK receipts. The reason to have 1000 amount in question, might pertain to a one receipt with an amount of 1000 and we need to raise an issue. That is why we showed it in a diffrent row. On the other hand, once we raised, 1200 meaning all of the receipts have same issue only and applied to all.&nbsp;&nbsp;I hope I answer your question 🙂&nbsp;Thanks and regards

jimmy801 · Answer

Hello Anonymous&nbsp;
&nbsp;
sorry, I don't get what you want to have calculated in your custom column.
Your data model shows this with values of 1200, 1200 1000 an d1200 in the colum AMOUNT IN QUESTION

&nbsp;
Your grouped table shows this

&nbsp;
I have to know how this column is grouped/created.
&nbsp;
BR
&nbsp;
Jimmy

anonymous · Answer

Hi&nbsp;Jimmy801&nbsp;,&nbsp;Good day.&nbsp;Apology on that. The table was created by using this formula in excel:&nbsp;"Per Original Unique Reference, if Spend Expense Amount (USD) based on Original (Y)&nbsp;is greater than&nbsp;Sum of&nbsp;Amount in Question, then value is equal the&nbsp;Spend Expense Amount. Otherwise, Sum of&nbsp;Amount in Question."&nbsp;To illustrate, for example:&nbsp;ORIGINAL UNIQUE REFERENCE# (sequence per line item, i.e. 1,2,3) of Inserted Line&nbsp;NEW UNIQUE REFERENCEORIGINAL (Y)Customer Name&nbsp;Spend - Expense Amount (USD)&nbsp;&nbsp;AMOUNT IN QUESTION&nbsp;SPECIFICAUDIT ISSUECurrent Audit StatusOrigUniqRef_01&nbsp;OrigUniqRef_01YCustomer A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,200.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,200.00Expense for ClarificationWith IssueOrigUniqRef_011OrigUniqRef_01.L1&nbsp;Customer A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,200.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,200.00Business Purpose Not ProvidedWith IssueOrigUniqRef_012OrigUniqRef_01.L2&nbsp;Customer A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,200.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,000.00Only Credit Card Slip/Statement is provided.With IssueOrigUniqRef_013OrigUniqRef_01.L3&nbsp;Customer A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,200.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,200.00No Time ChargesWith Issue&nbsp;The Spend Amount in (Y) is = 1200Sum Amount in Question = 4600Then, for this Uniq Ref#, the audit result amount will show = 1200. (Because the original amount for this particular Row is 1200 only).&nbsp;However, if there are errors in our formula, we check the audited file and edit it manually. For another example,&nbsp;ORIGINAL UNIQUE REFERENCE# (sequence per line item, i.e. 1,2,3) of Inserted Line&nbsp;NEW UNIQUE REFERENCEORIGINAL (Y)Customer Name&nbsp;Spend - Expense Amount (USD)&nbsp;&nbsp;AMOUNT IN QUESTION&nbsp;SPECIFICAUDIT ISSUECurrent Audit StatusOrigUniqRef_02&nbsp;OrigUniqRef_02YCustomer B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,500.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 900.00Receipt not providedWith IssueOrigUniqRef_021OrigUniqRef_02.L1&nbsp;Customer B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,500.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 900.00Expense for ClarificationWith Issue&nbsp;The Spend Amount in (Y) is = 1500Sum Amount in Question = 1800Technically, the amount must appear only is 900 because the only amount with raised issue is the receipt with 900 in amount. However, it appears wrong if we use the formula we created for this Audit Result column that is why other rows with the same kind of scenario, we edit it and correct it manually.&nbsp;I hope I got this one as answer to your question. 🙂&nbsp;Thanks and regards,Echo

anonymous · Answer

Hi&nbsp;Jimmy801&nbsp;&nbsp;Good day.&nbsp;Just to add, any suggestion is open if you have as we are still on our process of developing our report team.&nbsp;Thanks

Forum Discussion

Create a new column based on distinct result

9 Replies

Related content

Distinct count of users via Created By column

Create columns group

Distinctive Count across two columns

count distinct

Create New Table Based on DIstinct Values of One Column and Calculated Values of Another Column