Calculated measure by row I cannot get it to work ...

[BieBel]

I have to calculate the room surfaces for different room types => RM[RM_CAT] is not BATHROOM or BASEMENT.
I got the measure to 'work' as far as the roomtypes, but I need to (re)group the total surface by building RM[BL].
So I have to add another part to my measure. Alas, I can't get it to work.
Can anyybody please help?

AREA_T = CALCULATE(
SUM(RM[AREA]),
FILTER(RM,
(RM[RM_CAT] <> "BATHROOM") ||
(RM[RM_CAT] <> "BASEMENT")
))

...

[Anonymous]

Hi @BieBel,

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Vinay Pabbu

[Anonymous]

Hi @BieBel,

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Vinay Pabbu

[Anonymous]

Hi @BieBel,

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Vinay Pabbu

[BieBel]

rm_type	(Multiple Items)
Row Labels	Sum of area
0008	9,89
0013	302,75
0014	1,58
0015	7,8
0016	992,73
0018	566,51
0024	307,39

  rm_type has been filtered in this pivot

[Thejeswar]

Hi @BieBel ,

As I understand, you want data in details for all room types except Basement and Bathroom while your total needs to be at bl_id with all rooms considered.

You can use the below DAX to get it

RoomArea = IF(ISINSCOPE(Table1[rm_type]), CALCULATE([TotalArea], FILTER(Table1, AND(Table1[rm_type] <> "BASEMENT", Table1[rm_type]<>"BATHROOM"))), IF(ISINSCOPE(Table1[bl_id]), CALCULATE ( [TotalArea], ALLEXCEPT ( 'Table1', 'Table1'[bl_id] ) )))

TotalArea = SUM(Table1[area])

If this solves your question, mark it as solution and appreciate with a Kudo !

Regards,

[BieBel]

bl_id	rm_fl_bl	rm_type	area
0008	0008-00-00.05	TECHNICAL	9,89
0013	0013-00-00.05	PARKING	57,87
0013	0013-00-00.07	WORKSHOPS	170,2
0013	0013-00-00.14	PARKING	31,24
0013	0013-00-00.15	PARKING	32,3
0013	0013-00-00.19	STAIRS	2,15
0013	0013-00-00.23	STORAGE	3,34
0013	0013-01-01.01	BATHROOM	40,37
0013	0013-01-01.04	BATHROOM	13,56
0013	0013-01-01.05	STORAGE	3,73
0013	0013-01-01.07	STAIRS	1,92
0013	0013-02-02.01	BASEMENT	92,79
0014	0014-00-00.12	TECHNICAL	1,58
0015	0015-00-00.22	TECHNICAL	4,31
0015	0015-00-00.36	TECHNICAL	3,49
0016	0016-00-00.01	PARKING	43,2
0016	0016-00-00.07	PARKING	134,87
0016	0016-00-00.08	PARKING	53,89
0016	0016-00-00.21	BATHROOM	45,18
0016	0016-00-00.26	WORKSHOPS	53,21
0016	0016-00-00.28	OFFICES	15,44
0016	0016-00-00.34	WORKSHOPS	660,84
0016	0016-00-00.36	STORAGE	14,73
0016	0016-00-00.41	STORAGE	16,55
0018	0018-00-00.01	STAIRS	3,03
0018	0018-00-00.09	STORAGE	4,47
0018	0018-00-00.11	RESTAURANT	55,27
0018	0018-00-00.13	STAIRS	2,23
0018	0018-00-00.14	WORKSHOPS	262,13
0018	0018-00-00.16	STAIRS	0,98
0018	0018-00-00.22	STORAGE	8,18
0018	0018-M01-M01.01	STAIRS	2,34
0018	0018-M01-M01.05	PARKING	226,49
0018	0018-M01-M01.06	STAIRS	1,39
0018	0018-M01-M01.07	BATHROOM	15,9
0018	0018-M01-M01.08	BATHROOM	32,83
0021	0021-00-00.24	BATHROOM	30,45

[BieBel]

bl_id		every building has got it's 4-digit ID
rm_fl_bl		identifies a single room on a single floor in a single building
rm_type		type of room
area		m²

[BieBel]

[danextian]

Hi @BieBel 

I am not sure what exactly you want to achieve as you did not provide a sample data and your expected result from the same but try these:

AREA_ = 
CALCULATE (
    SUM ( RM[AREA] ),
    KEEPFILTERS ( NOT RM[RM_CAT] IN { "BATHROOM", "BASEMENT" } )
)

AREA_BY_BUILDING = 
CALCULATE ( [AREA_], ALLEXCEPT ( RM, RM[BL] ) )

Please see the attached sample pbix.

Otherwise, please provide a workable sample data (not an image), your expected result from the same sample data and your reasoning behind. You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud.

[BieBel]

 

Hi, thanks ...

I applied your code, buth both measures return the same numbers (no division by BL_ID)

I know you requested an excel or Power BI, yet I cannot figure out how to share it here. So I did take snaps. The table in Power BI is called 'RM'. Field names are unchanged.

[danextian]

Hi @BieBel 

Both measures are expected to return the same result at the bl_id level as the result of the first measure is applied to all rows for each bl_id unless you meant something else by this statement:

I have to calculate the room surfaces for different room types => RM[RM_CAT] is not BATHROOM or BASEMENT. I got the measure to 'work' as far as the roomtypes, but I need to (re)group the total surface by building RM[BL].

If you want the total area for each bl_id regardless of the room type, you can try this:

Area by bl_id =
CALCULATE ( SUM ( 'Table'[area] ), ALLEXCEPT ( 'Table', 'Table'[bl_id] ) )

If this isnt' what you're looking for, please show us your expected result given the same sample data.

[Thejeswar]

Hi @BieBel ,
Snaps will not help.

You can just copy your excel data and past it here. It will get pasted as text that can be copied and used

Regards,