DAX formula to measure or calculate distinct amount value from repeated unique ID

[Anonymous]

I'm trying to figure out the DAX code to measure or create column showing only the highlighted .

I have repeated same value of amount converted and SFDC number. I will need the value to be measured or calculated only once, shown in the attach image below. What is the best approach for this ?

Manually deleting row by row will be very cumbersome or is there are DAX code to delete repeated SFDC number or amount converted ?

My SFDC number in this Winson_test file will be the unique identifier or relationship joint to my other source.

My main objective is to have a chart showing : Amount converted vs Quarter with it's respective Product Line as the legend.

The source file is from Slaesforce Report. The attach image below is just an ilustration of a partial content after extracted from Salesforce Report.

[v-chuncz-msft]

@Anonymous,

Show us a complete example and the expected output.

[pablobarra]

Hi @BDA2,

From a  Salesforce perspective, it seems you are pulling data from a report built based on "opportunity + quotes" or similar. 

If this is the case, that is why you see 2 fields that can not be compared directly, "Product line" and "Amount converted".

"Product line" makes sense at quote level (not at opportunity header level), the relatinship is one to many, one record (opportunity) to one quote with many products (with different "Product line").

If that's the case, "amount converted" is calculated at opportunity header level, it is a total that is a single value and calculates the sum of all prodcuts in the quote, it will be the same value for all products.

Some options (4):

a) If you need "Product line" or any other information at Quote level, you can use a converted Amount at quote level and pull that to Power BI

b) you can create a formula field in SFDC to calculate the "Amount Converted" for each "Product line" and use those precalculated fields in Power BI

c) If you do not need "Product line", you can build the SFDC reporte starting from "opportunity" instead of "Opportunity and quotes"

d) In Power BI query editor you can remove the column Product line and apply a step to remove duplcated rows

Pablo 

[Anonymous]

Hi Pablo, @pablobarra
 
Appreciate your reply. Yes you are right. Both report are based on opportunity + quotes. My opportunity level does not shows product line. Only in quote level I can see the product line.
I actually intend to pull data from Saleforce Object level instead of Salesforce report with 2000 row limit. However, the complexity of my organization's Salesforce has predesigned the object with several relationship and layers. 
 
I am still at the testing stage to see which approach works effectively and wihout the need to engage Salesforce developer.
My early hypothesis suggests that in the long run pulling data at customized object field will be the best apporoach.
This will include a panel of team to execute.
 
Meanwhile, to answer your options
Some options (4):
a) If you need "Product line" or any other information at Quote level, you can use a converted Amount at quote level and pull that to Power BI (Done that but my amount value will repeat) My relationship is many to many pulling at quote level and relate to Opportunity level will create sum of repeated values.
b) you can create a formula field in SFDC to calculate the "Amount Converted" for each "Product line" and use those precalculated fields in Power BI ( I have limited access to Salesforce as a developer or advance administrator) I am still discussing to create relationship on opportunity level to have product line. At the moment only quote level has product line due to maximum layers.
c) If you do not need "Product line", you can build the SFDC reporte starting from "opportunity" instead of "Opportunity and quotes" ( I will need the product line ) Opportunity does not have product line, since the layers are beyond 3 level.
d) In Power BI query editor you can remove the column Product line and apply a step to remove duplcated rows ( Would you be able to advise further or assist on the DAX formula method)

[pablobarra]

Hi @Anonymous

Can you confirm that the relationship is many to many?

If it is many to many, then you can have one opportunity wiith MANY quotes (and products under each quote), and a given quote can be linked to MANY opportunity.

If you have one opportunity with many quotes and a quote can be linked to a single opportunity, the relationship is one to many.

To pull data directly from Salesforce object may be the best approach, but if you need the ammount converted, that may not BE available directly from Salesforce object, you may need a formula field.

If you can work without multicurrency, you can import all the objects to Power BI (Opportunity, quotes, products linked to quotes) and in Power BI query build the relation and create a sum based on ammount at "product quote" level.

This may not be a DAX problem, the details imported are at opportunity header level, but you need the values at product line level and DAX will not be able to split it.

Regards

Pablo

[Anonymous]

Hi Pablo @pablobarra

Appreciate your reply. Sorry for confusion and typo. It is one to many : One opportunity with MANY quotes and Products under each quote.

Meaning at the current stage pulling data through Salesforce report, I will need to create the formula at Salesforce object field. I tried creating formula at report level but was not successful.

I will need the multicurrency and likewise mentioned DAX will not be able to spilt it.

Many thanks I will discuss with my Salesforce team

Thank You

[prateekraina]

Hi @Anonymous,

Follow below steps:

Create a measure to calculate Max of Amount per Unique ID:

MaxAmount = MAX(Test[Amount])

Then Create a measure to sum the distinct amounts:

ActualAmount = SUMX(DISTINCT(Test[ID]),[MaxAmount])

Use this Actual Amount in your table to get correct Totals.

If you want to further understand how above DAX works, see this video.

Prateek Raina

[Anonymous]

Hi Pratee, @prateekraina
 
Apparently your measure works only for relationship which is unique one to one.
For my case my ID and Product Line is many to many, hence this measure approach will not work.
 
Is there any measure that works for many to many relationship.