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
Baroumy
Regular Visitor

Sumx DAX

Hi,

I'm having a problem with the sumx DAX which is resulting 0 for all rows. 

The DAX is as below :

Sales Price variance PY = sumx('Exploitation Values',[Prep Sales price variance P Y])
 
the Prep sales price formula is :
Prep Sales price variance P Y = [every sales qty EXP] * [U P VARIANCE PY]
 
the U P Variance PY formula is :
U P VARIANCE PY = IF([U P PY] = 0,0,[U P] - [U P PY])
 
the U P & UP PY formula are :
 
U P = sumx('Exploitation Values','Exploitation Values'[Sales ]) / sumX('Exploitation QTY','Exploitation QTY'[Sales ])
 
U P PY = CALCULATE([U P],SAMEPERIODLASTYEAR('Calendar'[date])).
 
I m using the sumx for the Prer sales price variance to correct the rows' totals. 
 
Any help?Capture 1.png

 

 
 
 
 
1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @Baroumy 
You need to summarize the fact table by the slicing by columns:

1.png

Sales Price variance PY =
SUMX (
    SUMMARIZE ( 'Exploitation Values', [Column1], [Column2] ),
    [Prep Sales price variance P Y]
)

View solution in original post

3 REPLIES 3
tamerj1
Super User
Super User

Hi @Baroumy 
You need to summarize the fact table by the slicing by columns:

1.png

Sales Price variance PY =
SUMX (
    SUMMARIZE ( 'Exploitation Values', [Column1], [Column2] ),
    [Prep Sales price variance P Y]
)

Thanks  @tamerj1 . I summarized them. Now at least I got figures :). But they have a wrong column total.

My point of using the Sumx is to get the correct total which should be 72,004.96 and not 1,490,790.74 (which is the total of the Actual Qty times the total of U P Variance PY).Capture2.PNG

 Small hint, the list of items doesn't appear in the year 2022 (the previous year) if I only select them in a table visual. However, when I start adding the measure like [U P] and so.. they appear ( when I choose the year 2022 in the calendar filter). I don't know if that's related.

Actually, it worked. I changed the column selections!

Appreciated @tamerj1 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors