topic Re: Want to create a Measure, Value want to insert in Rows under category in DAX Commands and Tips

Want to create a Measure, Value want to insert in Rows under category

DeepakSharma_01 — Tue, 21 Jan 2025 04:48:06 GMT

Hi Everyone,
I have data in sales table, which look like,

Region	REV_REDUCTION_TYPE	Gross Amount	Net Amount
Africa		$17730094.9	$15050089.1
Africa	Gift Card	$0.0	($7375.0)
Africa	Samples	$0.0	$2436.3
Africa	Thorne Advisor	$0.0	($2280.0)
Africa	UNKNOWN	$0.0	($145585.7)

and if you notice their is a difference between Gross amount & Net Amount for blank Rev_Reduction_Type that is off_Invoice.

so Off_Invoice = Gross - Net where Rev_reduction_type is blank, but I want show result table like this and I want do only using measures.

Region	REV_REDUCTION_TYPE	Gross Amount	Net Amount
Africa		$17730094.9	$15050089.1
Africa	Gift Card	$0.0	($7375.0)
Africa	Samples	$0.0	$2436.3
Africa	Thorne Advisor	$0.0	($2280.0)
Africa	UNKNOWN	$0.0	($145585.7)
Africa	Off_invoice	$0.0	$2680005.8

Thank You Everyone.

Re: Want to create a Measure, Value want to insert in Rows under category

bhanu_gautam — Tue, 21 Jan 2025 06:42:51 GMT

DeepakSharma_01 , First create a measure

Off_Invoice =
CALCULATE(
SUM('Sales'[Gross Amount]) - SUM('Sales'[Net Amount]),
'Sales'[REV_REDUCTION_TYPE] = BLANK()
)

Than create a measure to display the Net Amount including Off_Invoice:

Net Amount with Off_Invoice =
IF(
ISBLANK('Sales'[REV_REDUCTION_TYPE]),
SUM('Sales'[Net Amount]) + [Off_Invoice],
SUM('Sales'[Net Amount])
)

Create a measure to display the REV_REDUCTION_TYPE including Off_Invoice:

REV_REDUCTION_TYPE with Off_Invoice =
IF(
ISBLANK('Sales'[REV_REDUCTION_TYPE]),
"Off_invoice",
'Sales'[REV_REDUCTION_TYPE]
)

Add Region to the Rows.
Add REV_REDUCTION_TYPE with Off_Invoice to the Rows.
Add Gross Amount to the Values.
Add Net Amount with Off_Invoice to the Values.

Re: Want to create a Measure, Value want to insert in Rows under category

sanalytics — Tue, 21 Jan 2025 11:50:29 GMT

DeepakSharma_01
You can achieve the solution by using a Disconnected table and measures. Below is the code and steps.
Steps 1 :
Create a disconnected calculated table using below code

DisconnectedTable = VAR _1 = ADDCOLUMNS( SUMMARIZE( 'Table','Table'[Region],'Table'[REV_REDUCTION_TYPE] ), "Sort",RANKX( 'Table',[REV_REDUCTION_TYPE],,ASC ) ) VAR _HighestSort = ROW( "check", MAXX( _1,[Sort] ) ) VAR _2 = ROW( "Region","Africa","REV_REDUCTION_TYPE","Off_invoice","Sort",_HighestSort +1) VAR _Result = UNION( _1,_2 ) RETURN _Result

Step2 : Create below measures

Net Amount = VAR _NM = CALCULATE( SUM( 'Table'[Net Amount] ), TREATAS( VALUES( DisconnectedTable[REV_REDUCTION_TYPE] ), 'Table'[REV_REDUCTION_TYPE] ), TREATAS( VALUES( DisconnectedTable[Region] ), 'Table'[Region] ) ) VAR _BGM = SUMX( FILTER( 'Table', 'Table'[REV_REDUCTION_TYPE] = BLANK() ), [Gross Amount] ) VAR _BNM = SUMX( FILTER( 'Table', 'Table'[REV_REDUCTION_TYPE] = BLANK() ), [Net Amount] ) VAR _Diff = _BGM - _BNM VAR _Result = IF( MAX( DisconnectedTable[REV_REDUCTION_TYPE] ) = "Off_invoice", _Diff,_NM ) RETURN _Result

Gross Amount = VAR _GA = CALCULATE( SUM( 'Table'[Gross Amount] ), TREATAS( VALUES( DisconnectedTable[REV_REDUCTION_TYPE] ), 'Table'[REV_REDUCTION_TYPE] ), TREATAS( VALUES( DisconnectedTable[Region] ), 'Table'[Region] ) ) VAR _Diff = IF( ISBLANK( [Net Amount] ), BLANK(), 0 ) VAR _Result = IF( MAX( DisconnectedTable[REV_REDUCTION_TYPE] ) = "Off_invoice", _Diff,_GA ) RETURN _Result

below screenshot

i have test this without blank reduction type as well

Attached pbix file.

Hope it will help.

Regards

sanalytics

Re: Want to create a Measure, Value want to insert in Rows under category

DeepakSharma_01 — Tue, 21 Jan 2025 13:25:50 GMT

Hi bhanu_gautam ,
Solution is not working properly, and I think

REV_REDUCTION_TYPE with Off_Invoice =
IF(
ISBLANK('Sales'[REV_REDUCTION_TYPE]),
"Off_invoice",
'Sales'[REV_REDUCTION_TYPE]
)

This measure will return only "Off-Invoice" Flag not the result.

But Thank you for your response.

Re: Want to create a Measure, Value want to insert in Rows under category

DeepakSharma_01 — Wed, 05 Feb 2025 11:34:02 GMT

Hi sanalytics ,
Thank you for your response.

I gone through the entire solution but I am looking for solution based on Measures without using any calcullated Table.
I am using Direct Lake semantic model so I can not create any calculated table.

Re: Want to create a Measure, Value want to insert in Rows under category

sanalytics — Thu, 13 Feb 2025 08:42:10 GMT

DeepakSharma_01

Something like below screenshot

if yes.. Please download the below pbix file.

I have not used any calculated table but only measure..

Regards

sanalytics