Sum of unique invoice values in a calculated table - Help Needed

[AmeenVanakar]

Hi,

I am trying to get the total invoice dollar value for all the unique invoices in a calculated table. Is there an easy way to get this done?

I've created a new table & bought all the unique invoices (i.e. BILL NO) in a column using the below formula

Unique Bill Number = DISTINCT(Customer_Data[BILL NO])

However, I cannot get the sum dollar value for each of the distinct invoices. Any help will be great.

Here is a placeholder formulare for reference.

Bill Value = SUM(Customer_Data[Value])

[AmeenVanakar]

@Ritaf1983 

I want to add a new column in an existing calculated table to show customer visits based on their unique mobile number & unique invoices (i.e. their visits)

Where:

The distinct count of invoice number should match the below condition & show the result in a new column named "visits".

1 = 1 Visit

2 = 2 Visits

3 = 3 Visits

4 = 4 Visits

>=5 = More Than 5 Visits.

I also want to use these values as a filter in the report later, hence I want this in a column format.

I have attached the data snip for you to refer to.

Screenshot 1

I also added a table with values (Screenshot 2) to show the range but I'm not sure how to bring this all together. Any help will be great.

Screenshot 2

[Ritaf1983]

Hi @AmeenVanakar 
It seems like dynamic segmentation
https://www.youtube.com/watch?v=FDa0I3J3h-c
if you need it to work according to the filters / slicers 
Or static if you want it as a calculated column :
https://www.youtube.com/watch?v=0KBR-aEVaxk

if it is something else 

please share a pbix or some dummy data  ( not a screenshot )that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

+

This is a new topic within the post, so it might be worth giving it its own separate post.

If my answer was helpful please give me a Kudos and accept as a Solution.

[Kedar_Pande]

@AmeenVanakar ,
Add a Column for Total Invoice Value:

Invoice Value = 
CALCULATE(
SUM(Customer_Data[Value]),
Customer_Data[BILL NO] = EARLIER('Unique Bill Number'[BILL NO])
)

If this helped, a Kudos 👍 or Solution mark would be great!

Cheers,

Kedar Pande

www.linkedin.com/in/kedar-pande

[AmeenVanakar]

Thanks @Ritaf1983 this solution worked flawlessly!

[Ritaf1983 · Accepted solution]

Hi @AmeenVanakar 
You can use the formula :

invoice_sum = CALCULATE(SUM('Table'[value]),FILTER('Table','Table'[invoice]=EARLIER('Table'[invoice])))

The pbix with the example is attached

If my answer was helpful please give me a Kudos and accept as a Solution.