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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Chedva
Helper II
Helper II

ABS by product Platform

Hi,

 

I have the following Dataset:

 

PlatformProductAct (Sum measure)Forecast (Sum Measure)ABS (ABS = ABS('Actual Consol'[Act]-[Forecast]))
AA1802060
AA2405616
BB112015030
CC1514
CC2101
CC3341
CC4473
CC5958114

 

 

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
 
 
PlatformActForecastABSACC ABSABS by Product - want
A12076442976
B120150302930
C10893152923
Total3483192929129

 

Thanks!

6 REPLIES 6
v-yingjl
Community Support
Community Support

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:

table relationship.png

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

table result.png

 

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
Super User
Super User

Just use SUM('Table'[ABS])

If you put those items in a Table visualization.

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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@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!!!

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


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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
az38
Community Champion
Community Champion

@Chedva 

this doesn't work?

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

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
az38
Community Champion
Community Champion

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])
)

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.