Split values based on duplicate references

[Syndicate_Admin]

Hello

I am trying to get the final sale (after discounts), but in my current table each sale that has more than one product the total discount of the sale is repeated on each line generating that when I want to determine the total of the sale it throws me an incorrect total since it adds the total discount for each product line.

ex. For sale TGA1-20210424-115458, the total discount for the sale should be $381.4, but repeat it for each of the lines, I would like to know if I can create a formula to create a column that divides 'Discount Sale GST Exc' by the number of times the 'Reference' column is repeated.

[Anonymous]

HI @cecitorresHF,

If you are work with the default aggregation function of raw table fields, it is impossible to achieve your requirement, I think you need to use DAX expressions to write a formula.

Can you please share some dummy data with a similar data structure and expected results? It will help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[Syndicate_Admin]

Here I add a screenshot of what I'm looking to do, basically I need to create a column in the data table (discount) in which the discount is divided by the number of products purchased by sale (code), so that in the end the sales table looks like in the following image

[Ashish_Mathur]

Hi,

You do not need to create a discount column for that.  To your Table/matrix table visual, just drag The Reference field and write this measure to get the discount

Discount = min(Data[Sale discount])

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Syndicate_Admin]

Can you tell me how to share the link?

[Ashish_Mathur]

Hi,

If the Discount sale GST exc is a SUM measure, then replace SUM with MIN.

[Syndicate_Admin]

It is not a measure, that is information that the database.