Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.