cancel
Showing results 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

Regular Visitor

## using average and SUM

 INVOICE STEP INVOICEDATE TASKENDTIME DAYSSPENT 22093C Process Invoice 2022-04-29 41:00.0 5 22093C Super Approval 2022-04-29 14:00.0 37 22093C GL Stamp Job/Subcontractor 2022-04-29 41:00.0 0 22093C Final Approval 2022-04-29 41:00.0 0 22093C PM Approval 2022-04-29 21:00.0 0 22093C VP Approval 2022-04-29 22:00.0 0 22093C Invoice Coder Not Found 2022-04-29 10:00.0 0 22113HB Process Invoice 2022-05-17 52:00.0 93 22113HB VP Approval 2022-05-17 31:00.0 7 22113HB Super Approval 2022-05-17 48:00.0 28 22113HB Final Approval 2022-05-17 09:00.0 0 22113HB GL Stamp Job/Subcontractor 2022-05-17 53:00.0 0 22113HB PM Approval 2022-05-17 55:00.0 28

Hello everybody,

I am new to DAX and I searched a lot for my question but I was not successful to get an answer.

I put my table here.

I want to have a new coloumn which shows me the SUM of DAYSSPENT on unique INVOICE numbers, and then I want to be able to have an AVERAGE of those SUM.

1 ACCEPTED SOLUTION
Super User

If the columns on the screenshot above match your expectations, you can achieve such results using two similar calculated columns:

``````SUM_DAYSSPENT =
VAR CurrentInvoice = [INVOICE]
RETURN SUMX ( FILTER ( ALL(data), [INVOICE] = CurrentInvoice ), [DAYSSPENT] )

AVG_DAYSSPENT =
VAR CurrentInvoice = [INVOICE]
RETURN AVERAGEX ( FILTER ( ALL(data), [INVOICE] = CurrentInvoice ), [DAYSSPENT] )``````

Best Regards,

Alexander

Super User

If the columns on the screenshot above match your expectations, you can achieve such results using two similar calculated columns:

``````SUM_DAYSSPENT =
VAR CurrentInvoice = [INVOICE]
RETURN SUMX ( FILTER ( ALL(data), [INVOICE] = CurrentInvoice ), [DAYSSPENT] )

AVG_DAYSSPENT =
VAR CurrentInvoice = [INVOICE]
RETURN AVERAGEX ( FILTER ( ALL(data), [INVOICE] = CurrentInvoice ), [DAYSSPENT] )``````

Best Regards,

Alexander

Announcements

#### 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 Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors