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

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.

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.

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,

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.

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

12 Replies

danextian
Super User
1 year ago
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.
area by building.pbix20 KB
- BieBel
  Helper I
  1 year ago
  
  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.
  - Thejeswar
    Super User
    1 year ago
    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,
BieBel
Helper I
1 year ago
BieBel
Helper I
1 year ago
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

Helper I

1 year ago

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
Helper I
1 year ago
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
  Super User
  1 year ago
  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,
Anonymous
Not applicable
1 year ago
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
  Not applicable
  1 year ago
  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
    Not applicable
    1 year ago
    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

Forum Discussion

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

12 Replies

Related content

DAX Calculated Measure

DAX Measure for Ranking the valid rows and calculating the average rating

Measure or Calculated Column

Measure to calculate

Measure/Model calculation support needed

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²

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