cancel
Showing results for
Did you mean:

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

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?

1 ACCEPTED SOLUTION
Super User

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

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

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

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

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

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.

Regular Visitor

Actually, it worked. I changed the column selections!

Appreciated @tamerj1

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.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### 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