Forum Discussion

AnaCBaumgratz11's avatar
AnaCBaumgratz11
Frequent Visitor
8 years ago

Distinct values based another column with duplicated rows

  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 ...
Need Help
v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

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's avatar
AnaCBaumgratz11
Frequent Visitor
8 years ago

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