cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

New Member

## DAX

Hi all, just started using PowerBI in reporting and I need your help 🙂

I have sales and forecast by product, customer and week.

I need to calculate forecast absolute deviation over any filter context such that deviation over customer can cancel out but over product deviation accumulates.

Here is sample calculation

 Product Customer Week forecast sales deviation A X 1 20 18 2 A X 2 18 20 -2 A Y 1 33 27 5 A Y 2 16 10 6 B X 1 15 11 4 B X 2 24 28 -4 B Y 1 40 26 14 B Y 2 24 22 2 Total 27

I need a measure 'absolute deviation' such as that the total/sum 'absolute deviation' is 31 instead of 27

and  'absolute deviation' over week is such that

 Week absolute deviation 1 25 2 6 Total 31

and  'absolute deviation' over product and week is such that

 product Week absolute deviation A 1 7 A 2 4 B 1 18 B 2 -2 Total 31

Any leads on how I can write this measure is welcome....

Thanks
Gathoni

1 ACCEPTED SOLUTION
Community Support

Hi @Gathoni ,

According to your description, I assume that you need the ABS, you can try this method:

Create a new table to calculate.

New table:

``````Table 2 =
SUMMARIZE (
'Table',
'Table'[Week],
'Table'[Product],
"absolute deviation", SUM ( 'Table'[deviation] )
)
``````

In the table, add a new column:

``ABS = ABS('Table 2'[absolute deviation])``

Then in the Table, create three measures:

``ABS = SUM('Table 2'[ABS])``
``NOTABS = SUM('Table 2'[absolute deviation])``
``````Result =
IF ( HASONEVALUE ( 'Table 2'[absolute deviation] ), [NOTABS], [ABS] )
``````

The result is :

Hope this helps you.

Best Regards,

Community Support Team _Yinliw

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

2 REPLIES 2
Community Support

Hi @Gathoni ,

According to your description, I assume that you need the ABS, you can try this method:

Create a new table to calculate.

New table:

``````Table 2 =
SUMMARIZE (
'Table',
'Table'[Week],
'Table'[Product],
"absolute deviation", SUM ( 'Table'[deviation] )
)
``````

In the table, add a new column:

``ABS = ABS('Table 2'[absolute deviation])``

Then in the Table, create three measures:

``ABS = SUM('Table 2'[ABS])``
``NOTABS = SUM('Table 2'[absolute deviation])``
``````Result =
IF ( HASONEVALUE ( 'Table 2'[absolute deviation] ), [NOTABS], [ABS] )
``````

The result is :

Hope this helps you.

Best Regards,

Community Support Team _Yinliw

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

Solution Sage

1. The 3rd line of your sample data shows [deviation] as 5 when it should be 6.
2. I can’t figure out what you mean by “absolute deviation” and its total being equal to 31.

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors