Forum Discussion
DeepakSharma_01
1 year agoFrequent Visitor
Want to create a Measure, Value want to insert in Rows under category
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) Afr...
sanalytics
Super User
1 year agoDeepakSharma_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
_ResultGross 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
1 year agoFrequent Visitor
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.
- sanalytics1 year ago
Super User
Something like below screenshot
if yes.. Please download the below pbix file.
I have not used any calculated table but only measure..
Regards
sanalytics