Sum value of column if contain specific text and display in table

[Tevon713]

Hi All. 

I have simple data as follow.  I want to sum up the Visit Amt if the Room Type is "ROOM".

Created this measure, Total VisitTime = CALCULATE(SUM('Table1'[VisitAmt]),FILTER('Table1', Table1'[Room Type]="ROOM"))

Acct Num	Date	Room Type	Visit Amt
1	8/1/2022	ROOM	37
2	8/1/2022	ROOM	53
3	8/1/2022	Living	68
4	8/1/2022	ROOM	41
5	8/2/2022	ROOM	61
6	8/2/2022	ROOM	52
7	8/2/2022	Conference	67
8	8/2/2022	Conference	40
9	8/2/2022	ROOM	98
10	8/2/2022	ROOM	73
11	8/3/2022	ROOM	78
12	8/3/2022	Conference	65
13	8/3/2022	Living	54
14	8/3/2022	ROOM	61
15	8/4/2022	ROOM	40
16	8/4/2022	ROOM	78
17	8/4/2022	ROOM	34
18	8/4/2022	ROOM	67
19	8/4/2022	ROOM	58
		Total	1,125

Ideally should look like this... I'm getting something different when pushing in table or matrix giving the total for listed date.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-yanjiang-msft]

Hi @Tevon713 ,

My test result is the same with @MDodds 's. An alternative, you can simply only select ROOM in the visual filter of Room Type column, and just put the VisitAmt column in the visual without a measure.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[MDodds · Accepted solution]

Hi Tevon,

I tried recreating this using your data and my formulas seem to work, although they are no different from yours.
Only thing I can see is some minor differences in your syntax re apostrophes. Try my DAX below:

Room Only = Calculate(Sum(Table1[Visit Amt]),Filter(Table1, Table1[Room Type] = "ROOM"))
Produces the following result:

If still stuck I can either send you my PBIX where I tried this out, or you could send me yours for review?

Cheers

[MDodds]

Link to PBIX if helpful. Cheers

https://www.dropbox.com/s/oi87ojirmhg3a58/Tevon%20-%20PowerBI%20Community%20Room%20Only%20Solution.p...