Distinct values based another column with duplicated rows

[AnaCBaumgratz11]

I have a table of expenses by customer and prefunding received bye each one.

Then, I need to calculate % received by each customer and the average by country.

However, the table has a row by ID, but Proforma column is by expense (then I have the same ID for several costs), and the PrefundingRec column repet the value for each row (only one prefunding received for each customer); and each ID service can have more than one customer.

I concatenated ID with Customer to have a unique Index (ID_Customer) and a Total Proforma (sum [Proforma]). However when I try divide PrefundingRec by Total Proforma, the result still multiply for the total row. I also tried to create a new table with ID, Customer and PrefundingRec; but I still cannot do the average calculation by country.

I'm new on Power BI, then I'm not sure how to solve this.

I appreciate if someone can help me.

Regards,

Ana

[v-yulgu-msft]

Hi @AnaCBaumgratz11,

Based on my understanding, I generated below calculated column.

divide =
CALCULATE (
    AVERAGE ( Test_1[PrefundingRec] ),
    ALLEXCEPT ( Test_1, Test_1[ID], Test_1[Customer] )
)
    / CALCULATE (
        SUM ( Test_1[Proforma] ),
        ALLEXCEPT ( Test_1, Test_1[ID], Test_1[Customer] )
    )

---

 Then, I need to calculate % received by each customer and the average by country. 

---

Please illustrate your desired output with examples and images.

Regards,

Yuliana Gu

[AnaCBaumgratz11]

Hi @v-yulgu-msft

Thank your for your message.

I tried your suggestion, but it didn't work.

Please, see below my question with more details.

 I have the table below, where I have different services/sales (ID) by customer (Customer Name). Each ID means an attendance, which can be for more than one customer and related with one or more services type (Customer Cost Allocation). Then, I have a Proforma (costs paid, each row is one cost) and a Prefunding received (this is the total by ID, Customer Name and Customer Cost Allocation). The point is that the Prefunding is repeated in each row, when this is different for the same "ID, Customer Name and Customer Cost Allocation", it's because we received more than one remittance, and then I need to sum the values only if these are different.

 

Then I want to measure, difference of Prefunding to Proforma, value, as per field "Var." below, which would be sum of prefunding (only with different values) by each ID and Customer; and percentage, as per "Var. Perc." field below, which is sum of prefunding (only with different values) divided by sum of proforma (all values). See the results below.

Below is the result I expected to get for the average of % of Prefunding received by country.

Thanks in advance for your kind help.

Regards,

Ana B