cancel
Showing results for
Did you mean:
Regular Visitor

## Sum of a measure

Hi all and thank you for any advice

Relatively new to all this and have hit a wall! I am working with SQL as my data source and I have three tables, filtered by the slicer.

The name is me so not too worried that is showing! The first table is a countrows of the class code, the second is a countrows of user id in those classes. The third contains a measure that multiplies periods by pupils, by class. All good so far. The totals in the first two tables are fine, and I understand why the total in the third table is what it is, but that is not what I want. My plan is to turn the total off in the third column and what I would like to do is have the sum of the commitments column of the filtered data in a card.

I have read quite a few posts but struggling to make sense of it all.

ANy help gratefully recieved.

Apologies if I have waffled!

1 ACCEPTED SOLUTION
Super User

If you're not using a measure then you need CALCULATE to force context transition. Try

``````Commitments by class v2 =
SUMX (
VALUES ( 'Timetable Schedule'[txtCode] ),
CALCULATE ( COUNTA ( 'Set Lists'[txtSchoolID] ) )
* CALCULATE ( COUNTA ( 'Timetable Schedule'[txtCode] ) )
)
``````
4 REPLIES 4
Regular Visitor

Amazing, that worked!!! Thank you so much.

Super User

I think you want something like

``````Commitment by class =
SUMX ( VALUES ( 'Table'[Class] ), [Pupils] * [Periods] )
``````
Regular Visitor

Thank you for your help so far. I went with

Commitments by class v2 = SUMX ( VALUES ( 'Timetable Schedule'[txtCode] ), COUNTA('Set Lists'[txtSchoolID]) * COUNTA('Timetable Schedule'[txtCode]) )

but that gave me an even larger total! Think I am going wrong somewhere.
Super User

If you're not using a measure then you need CALCULATE to force context transition. Try

``````Commitments by class v2 =
SUMX (
VALUES ( 'Timetable Schedule'[txtCode] ),
CALCULATE ( COUNTA ( 'Set Lists'[txtSchoolID] ) )
* CALCULATE ( COUNTA ( 'Timetable Schedule'[txtCode] ) )
)
``````

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

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

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors