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

[DeepakSharma_01]

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.

[sanalytics]

@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

[DeepakSharma_01]

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.

[sanalytics]

@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

[bhanu_gautam]

@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.

[DeepakSharma_01]

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.