Forum Discussion

DeepakSharma_01's avatar
DeepakSharma_01
Frequent Visitor
1 year ago

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

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

RegionREV_REDUCTION_TYPEGross AmountNet Amount
Africa $17730094.9$15050089.1
AfricaGift Card$0.0($7375.0)
AfricaSamples$0.0$2436.3
AfricaThorne Advisor$0.0($2280.0)
AfricaUNKNOWN$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.

RegionREV_REDUCTION_TYPEGross AmountNet Amount
Africa $17730094.9$15050089.1
AfricaGift Card$0.0($7375.0)
AfricaSamples$0.0$2436.3
AfricaThorne Advisor$0.0($2280.0)
AfricaUNKNOWN$0.0($145585.7)
AfricaOff_invoice$0.0$2680005.8


Thank You Everyone.


5 Replies

  • 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 , 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's avatar
      DeepakSharma_01
      Frequent 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.