ABS by product Platform

[Chedva]

Hi,

I have the following Dataset:

Platform	Product	Act (Sum measure)	Forecast (Sum Measure)	ABS (ABS = ABS('Actual Consol'[Act]-[Forecast]))
A	A1	80	20	60
A	A2	40	56	16
B	B1	120	150	30
C	C1	5	1	4
C	C2	1	0	1
C	C3	3	4	1
C	C4	4	7	3
C	C5	95	81	14

I would like to calculate the ABS of the product platform but according to each product.

I tried this 

ACC ABS = CALCULATE(ABS([Contract Act]-[Contract Forecast]),ALLEXCEPT(Matrix_Lookup,Matrix_Lookup[Product])) but that gave me the total ABS for each line, 

See column ABS by Product, that is the resulet I want

Platform	Act	Forecast	ABS	ACC ABS	ABS by Product - want
A	120	76	44	29	76
B	120	150	30	29	30
C	108	93	15	29	23
Total	348	319	29	29	129

Thanks!

[v-yingjl]

Hi @Chedva ,

Could you please consider sharing the relationship and fields between these tables or a sample .pbix file for further discussion?  I have created the table relationship like this and create a measure to get your expected output but not certain your dataset relationship:

ABS by product = 
CALCULATE(
    SUMX(
        'act table',
        [ABS]
    ),
    ALLEXCEPT(
        'act table',
        'act table'[Platform]
    )
)

Best Regards,
Yingjie Li

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

[Greg_Deckler]

Just use SUM('Table'[ABS])

If you put those items in a Table visualization.

[Chedva]

@Greg_Deckler @az38 

ABS is a measure, not in a specific table. the table I showed is a table in the report section.

Act - a measure - that sums the measure from the actual table

Forecast - a measure that sums the forecast from forecast table.

Matrix_Lookup is a table with relationship to both actual table and forecast table on the product. and it has the platform there as well for slicing the report.

Hope I was clear - thanks for you help!!!

[Greg_Deckler]

Well then this looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

[az38]

@Chedva 

this doesn't work?

Measure = 
CALCULATE(
SUMX('Matrix_Lookup', ABS([Act]-[Forecast])), 
ALLEXCEPT(Matrix_Lookup, Matrix_Lookup[Platform])
)

[az38]

Hi @Chedva 

try this measure

Measure = 
CALCULATE(
SUMX('Matrix_Lookup', ABS('Matrix_Lookup'[Act (Sum measure)]-'Matrix_Lookup'[Forecast (Sum Measure)])), 
ALLEXCEPT(Matrix_Lookup, Matrix_Lookup[Platform])
)