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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Raul
Post Patron
Post Patron

Running total

Hello everybody,

I have a CALENDAR table in my data model. I have too two more tables: WAREHOUSES and TARGETS:

IDWAREHOUSE
AWarehouse1
BWarehouse2
CWarehouse3

 

YEARWAREHOUSETARGET
2017A $    5.000,00
2017B $ 10.000,00
2017C $ 20.000,00
2016A $    6.000,00
2016B $ 25.000,00
2016C $ 18.000,00

 

The TARGETS table is not related with de CALENDAR table but is related with the WAREHOUSES table. I have too a measure MonthTarget = SUM(Target)/12

 

I need the running total for Target field in this matrix visualization:

 

YEAR2017
WAREHOUSEA
MonthTarget
January $       416,67
February $       833,34
March $    1.250,00

  

The YEAR and WAREHOUSE fields are filters and the value of 416,67 is the MonthTarget value of the A warehouse for the year 2017. For a whole year it would be:

YEAR2017
WAREHOUSEA
  
MonthTarget
January $       416,67
February $       833,34
March $    1.250,00
--- $    1.666,67
--- $    2.083,34
--- $    2.500,00
--- $    2.916,67
--- $    3.333,34
--- $    3.750,00
--- $    4.166,67
--- $    4.583,34
December $    5.000,00

 

How can I calculate this measure?

Thank you.

 

 

 

1 ACCEPTED SOLUTION

In looking at your data layout and calculation, I would create a MONTH column in your Calendar table and do something like:

 

Measure = MonthTarget * AVERAGE(Calendar[Month])


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

7 REPLIES 7
Greg_Deckler
Super User
Super User

Have you tried the Running Total quick measure?



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Yes, but the result is the same value of the month target for every mont, like this:

MonthTargetRunningTotal
January $       416,67 $       416,67
February $       833,34 $       416,67
March $    1.250,00 $       416,67

 

Can you post that formula?



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

RunningTotal =
CALCULATE(
[MonthTarget];
FILTER(
ALLSELECTED('Calendar'[Month]);
ISONORAFTER('Calendar'[Month]; MAX('Calendar'[Month]); DESC)
)
)

In looking at your data layout and calculation, I would create a MONTH column in your Calendar table and do something like:

 

Measure = MonthTarget * AVERAGE(Calendar[Month])


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Great!!! It works perfectly.

Thank you very much @Greg_Deckler

Happy to help! 🙂



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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