cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AshDil
Helper V
Helper V

Need to show total sale value of ID in first month it appeared even it is present in other months

Hi,
I have data as follows:

DATE

ID

Value

1/1/2021

1011

100

1/1/2021

1012

100

2/1/2021

1011

200

3/1/2021

1011

250

8/1/2021

1020

400

9/1/2021

1011

300

12/1/2021

1011

220

Eg: ID = 1011, it is present in Jan, Feb,Mar,Sep,Dec.Need to show total sale value for ID in the first month it appeared in trend chart. Need to show total sale value of 1011 ID = 100+200+250+300+220 = 1070 in Jan Month (since it appeared first in Jan)

Please help me to do.

Thanks,

AshDil

2 ACCEPTED SOLUTIONS
parry2k
Super User
Super User

@AshDil change my measure to this and it will work

 

Measure = 
VAR __firstDate = CALCULATE ( MIN ('Table'[DATE] ), ALL ('Table' ), VALUES ('Table'[ID] ) )
VAR __sum = CALCULATE ( SUM ('Table'[Value] ), ALL ('Table' ), VALUES ('Table'[ID] ) )
RETURN 
IF ( MAX ('Table'[DATE] ) == __firstDate, __sum ) + 0

 

parry2k_0-1639452872628.png

 

Follow us on LinkedIn

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

18 REPLIES 18
parry2k
Super User
Super User

@AshDil change my measure to this and it will work

 

Measure = 
VAR __firstDate = CALCULATE ( MIN ('Table'[DATE] ), ALL ('Table' ), VALUES ('Table'[ID] ) )
VAR __sum = CALCULATE ( SUM ('Table'[Value] ), ALL ('Table' ), VALUES ('Table'[ID] ) )
RETURN 
IF ( MAX ('Table'[DATE] ) == __firstDate, __sum ) + 0

 

parry2k_0-1639452872628.png

 

Follow us on LinkedIn

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





parry2k
Super User
Super User

@AshDil how you are planning to visualize it? You should have explained it in your original post. The question you asked was solved but now you are asking something totally different.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Hi @parry2k  & @Ashish_Mathur ,

 

I need to show output as follows in trend chart

AshDil_1-1639449927899.png

 

Please help me to do it.

Thanks,

AshDil.

 

Hi,

How is this going to help.  I have requested you in my previous message to show the exact result in a column


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur ,

When I use ID column in the visual I need the Output as follows:

DATEIDValueResult
1/1/202110111001070
1/1/20211012100100
2/1/20211011200 
3/1/20211011250 
8/1/20211020400400
9/1/20211011300 
12/1/20211011220 

If I eliminate ID column in the visual the output I need is

DateResult
1/1/20211170
2/1/2021 
3/1/2021 
8/1/2021400
9/1/2021 
12/1/2021 

The purpose of this requirement is the ID will be created in one month with some amount (eg: ID = 1011 and month created = Jan and amount  = 100) going forward there will be some negotiation or addition of value to the original amount with time (if we moved from Jan to Feb there is addition of 200 to the ID 1011), so the user wanted to see entire sum for the ID in first month it created. As ID column will have many ID numbers, they want see month wise which is high level.

Please help me with this.

Thanks,

AshDil.

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @parry2k ,

Sorry for not giving that clarity in my post. I need to show the calculation in trend chart with out using ID field in the visual, Please help me with a way to show it in trend chart.

Thanks,

AshDil.

parry2k
Super User
Super User

@AshDil here is the measure which will get you going:

 

Measure = 
VAR __firstDate = CALCULATE ( MIN ('Table'[DATE] ), ALL ('Table' ), VALUES ('Table'[ID] ) )
VAR __sum = CALCULATE ( SUM ('Table'[Value] ), ALL ('Table' ), VALUES ('Table'[ID] ) )
RETURN 
IF ( MAX ('Table'[DATE] ) == __firstDate, __sum )

 

Follow us on LinkedIn

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Hi @parry2k ,

This measure has helped me to get what I was expecting but I'm trying to show this measure in Trend chart where I should not use ID field, in that case I'm getting blank visual. Please help me to do it.

Thanks,

AshDil.

Ashish_Mathur
Super User
Super User

Hi,

Not sure of why you want this, but enter this calculated column formula

=if(CALCULATE(countrows(Data),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[DATE]<=EARLIER(Data[DATE])))=1,CALCULATE(SUM(Data[Value]),FILTER(Data,Data[ID]=EARLIER(Data[ID]))),BLANK())

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur ,

 

I have tried to create a calculated column as you mentioned but some of the ID are missing with that calculation. Please help me to get all ID.

 

Thanks,

AshDil.

Hi,

I am not sure of what you want but try this calculated column formula

=CALCULATE(SUM(Data[Value]),FILTER(Data,Data[ID]=EARLIER(Data[ID]))

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur ,

I tried using this calculation,I'm getting all ID's this time but the values getting rolled up to high values. Please help me to do it.

Thanks,

AshDil

Hi,

Show the exact expected result in a spare column.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
parry2k
Super User
Super User

@AshDil is this what you are looking for?

 

parry2k_0-1639193521206.png

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Hi @parry2k,

 

Yes I want the output in same way.

 

Thanks,

AshDil. 

lbendlin
Super User
Super User

Use an ALLEXCEPT() filter on the ID when you calculate the SUM(Value).

Hi @lbendlin ,

 

I have tried using ALLEXCEPT(), getting total sum value of ID in each month that is present. Getting output as follows:

AshDil_0-1639364839815.png

Thanks,

AshDil.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.