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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
SushiSashimi1
Frequent Visitor

Measure to Total each Period Spent using Year to Date Spent for each service

Trying to create a dashboard - part to show total spent per period.  Unable to as all spent amount are year to date spent per serivce, hence all values either doubled, tripled or more.. 

Is there a way to sum the total per period?

Example of data as per below:

 

Period No Service YTD Total Spent
Jul-21 A 10000
Aug-21 A 11000
Sep-21 A 11100
Oct-21 A 15000
Nov-21 A 15500
Dec-21 A 20000
Jul-21 B 599
Aug-21 B 1000
Sep-21 B 3000
Oct-21 B 3500
Nov-21 B 5000
Dec-21 B 9000
Jul-21 C 0
Aug-21 C 400
Sep-21 C 900
Oct-21 C 1500
Nov-21 C 2000
Dec-21 C 3000

 

Thank you

3 REPLIES 3
v-yanjiang-msft
Community Support
Community Support

Hi @SushiSashimi1 ,

According to your description, in my understanding, you only have YTD spend for each period and service, now you want the total spend for each period and service.

Actually the last YTD data in that period is the total of that period

vkalyjmsft_0-1664786375313.png

If there're many years in your sample and you want to calculate total in each year, you can firstly create a custom column in Power Query.

vkalyjmsft_0-1664786959759.png

Year = [Period No]

Then select the custom column and click Extract>Text After Delimiter.

vkalyjmsft_1-1664787154870.png

vkalyjmsft_2-1664787312636.png

Get the year column.

vkalyjmsft_3-1664787346036.png

Then create a measure.

Measure =
MAXX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Service] = MAX ( 'Table'[Service] )
            && 'Table'[Year] = MAX ( 'Table'[Year] )
    ),
    'Table'[YTD Total Spent]
)

Get the result.

vkalyjmsft_4-1664787638167.png

I attach my sample below for your reference.

 

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Thank you for your guidance and sorry for not being very clear of what I wanting to achieve.

I am using your pbix and put in additional visual note to explain: multi select - Jul & Aug, it will just sum both value together... maybe it's an easy fix but I just cant my head around it.. 

SushiSashimi1_0-1664835628199.png

Total sum not correct:

SushiSashimi1_1-1664835657603.pngService A should be 11000, Service B should be 1000, Service B should be 400

 

lbendlin
Super User
Super User

What is the expected outcome based on your sample data?

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.