total doesnt work

[Anonymous]

Hi,

I have a PBI report (SQL Service Analysis direct connection) with a table showing number of surgeries by room, and slicer for RoomFilter.



table example:

SurgeryRoom NumOfSurgeries

a 1

b 1

c 1

total: 3



when I choose 2 rooms in the slicer, the total doesn't change.

table example:

SurgeryRoom NumOfSurgeries

a 1

b 1

total: 3



when i expect the total to be 2..



can you help with this problem?

thank you 🙂

[v-yuta-msft]

@Anonymous,

Click [SurgeryRoom] and [NumOfSurgeries] to the chart and click NumOfSurgeies-> Sum, then create a slicer based on [SurgeryRoom].

 

The result is like below:

 

Regards,

Jimmy Tao

[themistoklis]

@Anonymous

Try this formula:

Measure = SUMX (
                VALUES ( Table[SurgeryRoom] );
                CALCULATE ( SUM ( Table[NumofSurgeries] ) )
            )

[Anonymous]

thank you for you answer 🙂
where?
the total is automatic with the table..

[themistoklis]

@Anonymous

Createa new Measure and add the formula.

Right click on the table on the left hadn side --> New Measure

[Anonymous]

It doesnt exists in analysis connection ..

[v-yuta-msft]

@Anonymous,

It doesnt exists in analysis connection ..

---

 So you are using power bi live connection mode to connect to SSAS, right? If you are, you can create measure using SUM in SSAS side. 

Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Right, Im using live connection.
The "NumberOfSurgeries" is a measure in SSAS- its distinct count of surgery-code.
When im adding it to the table it works fine. the total is fine. when im adding a slycer/filter- the total doesnt react and the total keeps showing the total of all the rooms.
by the way, when im processing the cube and refresh the report- the total shows the right number. but when i add a room and remove ir from the slycer - the total shows the total of all the rooms agin

[v-yuta-msft]

@Anonymous,

In the format pane, select don't summarize.

Community Support Team _ Jimmy Tao

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