Forum Discussion
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) |
| 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.
5 Replies
- sanalytics
Super User
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 codeDisconnectedTable = 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 _ResultStep2 : 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 _Resultbelow screenshot
i have test this without blank reduction type as well
Attached pbix file.
Hope it will help.
Regards
sanalytics
- DeepakSharma_01Frequent 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.- sanalytics
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
- bhanu_gautam
Super User
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_01Frequent Visitor
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.