Total Revenue (in Groups)

vernonwyng · November 3, 2020, 10:38am

Hi, I am attempting to calculate the Total Revenue for specific ITMCODE from the Sample Table attached

Sample Table

and output them in a format as following:

Output File

I have already successfully calculated the count and Item Revenue, however I am not able to formulate a proper formula to generate the Total Revenue. I have attempted to use ALL function as below but failed to get the correct results. (I still yet need to add in the formula to evenly split non-specific ITMCODE revenue under the specific ITMCODE)

Total Revenue = SUMX(VALUES(‘Sample Table’[ITMCODE]), CALCULATE(SUM(‘Sample Table’[AMOUNT]), ALL(‘Sample Table’, ‘Sample Table’[ITMCODE])))

Thank you in advance.

sample table.pbix (28.7 KB)

Anurag · November 3, 2020, 10:44am

Can you provide a sample pbix

vernonwyng · November 3, 2020, 2:31pm

attached sample pbix file, thanks.

MudassirAli · November 3, 2020, 2:51pm

@vernonwyng
I have modified you measures as you don’t need an iterator functions for the required result

Item Revenue =
SUM(‘Sample Table’[AMOUNT])

Total Revenue =
CALCULATE(
SUM( ‘Sample Table’[AMOUNT] ),
ALLEXCEPT(‘Sample Table’,’ Sample Table’[ITMCODE] )
)

The final result is

The PBIX file is attached. Let me know if this answers your question
sample table.pbix (28.7 KB)

vernonwyng · November 3, 2020, 3:52pm

@MudassirAli

Thanks for the prompt reply, however the “Total Revenue” output for items A12, A13 should be $3,350 and $3,115 respectively.

The formula for calculating Total Revenue for A12 is as below, however I am not able to convert it to DAX language

Total Revenue for A12
= SUM of all AMOUNT in SLIP 111111 & 111112 + AMOUNT of A12 in 11114 + AVERAGE of remaining ITM in 111114 DIV by # of specific ITMCODE in 111114
= 1000+10+15+100+40+1000+20+25+30+35+40+1000+(50+20)/2 = 3350

MudassirAli · November 3, 2020, 4:30pm

@vernonwyng
Can you give some context as to why you want the specific type of calculation? I need the logic that I can build into the formula.

vernonwyng · November 4, 2020, 12:05am

@MudassirAli,

A SLIP # is assigned to each transaction, where
The ITMCODE column consist of a mixture of different types of items used within the transaction, e.g. procedure revenue, revenue from consumable items, rental equipment…

The problem is to get the total revenue generated by each procedure.

If the SLIP only have one procedure, I will need to sum all ITMCODE within the SLIP.
If the SLIP contains multiple procedures, I will need to average out the revenue of the non-procedure ITMCODE and evenly distribute it among the procedures.

Thank you.

MudassirAli · November 4, 2020, 1:51am

@vernonwyng
I am finding it difficult to understand the end result you are trying to achieve. What I have understood is that Items starting with A are procedural codes and the Integers in Items are Non-Procedural Codes.

Code	Category	Code	Category
A12	Procedural	3333	Non-Procedural
A13	Procedural	3334	Non-Procedural
		3335	Non-Procedural
		3336	Non-Procedural
		4444	Non-Procedural
		4445	Non-Procedural
		4446	Non-Procedural
		4447	Non-Procedural
		4448	Non-Procedural
		5555	Non-Procedural

The part that’s difficult to for me to rationalize is highlighted in bold:

It would be helpful if you can elaborate more on this.
Thanks

vernonwyng · November 4, 2020, 2:35am

@MudassirAli

As mentioned on the previous response,
The attempt is to evenly distribute the revenue generated by non-procedural code into each procedure category and avoid double counting.

Example:
Total revenue for A12 in slip 111114 would be $1,000+ ($50 +$20)/2 = $1,035
Total revenue for A13 in slip 111114 would be $1,500+ ($50 +$20)/2 = $1,535

Melissa · November 6, 2020, 1:46pm

Hi @vernonwyng,

Something odd’s going on here…

I can create the required logic in M

And obviously in a DAX table as well

BUT when I create the same DAX logic in a virtual table to create a Measure, I can’t seem to get it to work… even after trying to break lineage I come up empty maybe someone with fresh eyes can take another stab at that…

Anyway turning it into the smallest possible physical table, hooking it up to your model and hiding it from view - works. Although the relationship is not required, you can create that virtually.

Total Revenue (DAX table) = 
    SUMX( TempTable, [@SlipAmount] ) + [Item Revenue]

Although you’ll find 2 solutions in the file attached below, this is the Measure that’s bugging me and I can’t seem to get to work properly.

Total Revenue v2 (doesn't work) = 
VAR vAllItems = ALLNOBLANKROW( 'Specific ITMCODE'[ITMCODE] )
VAR vAllItems2 = 
    ADDCOLUMNS(
        ALLNOBLANKROW( 'Specific ITMCODE'[ITMCODE] ),
        "@NewItemCode", [ITMCODE] &""
    )
VAR vAllSlips = 
    ADDCOLUMNS(
        VALUES( 'Sample Table'[SLIP] ),
        "@Num of Items", CALCULATE( COUNTROWS( 'Sample Table' ), FILTER(  VALUES('Sample Table'[ITMCODE]), 'Sample Table'[ITMCODE] IN vAllItems ))
    )
VAR vSlipItems =
ADDCOLUMNS(
    ADDCOLUMNS(
        FILTER( SELECTCOLUMNS( 'Sample Table', "@Item", [ITMCODE], "@Slip", [SLIP] ),
            [@Item] IN vAllItems
        ), "@Num of Items", MAXX( FILTER(vAllSlips, [SLIP] = [@Slip] ), [@Num of Items] )
    ),  "AvgSlipRevenue", SUMX( FILTER('Sample Table', 'Sample Table'[SLIP] = [@Slip] && NOT('Sample Table'[ITMCODE]) IN vAllItems ), [AMOUNT] ) / [@Num of Items],
        "@NewItemCode", MAXX( FILTER(vAllItems, [ITMCODE] = [@Item] ), [@Item] ) &""
)
VAR vTable =
    SELECTCOLUMNS( vSlipItems, 
        "@Item", [@NewItemCode],
        "@AvgSlipAmt", [AvgSlipRevenue]
    )
VAR nTable =
    ADDCOLUMNS( SELECTCOLUMNS( vAllItems2, "Item", [@NewItemCode] ),
        "@Amount", SUMX( FILTER( vTable, [@Item] = EARLIER([Item])), [@AvgSlipAmt] )
    )
RETURN

CALCULATE( 
    SUMX( nTable, [@Amount] ),
    //FILTER( SELECTCOLUMNS( nTable, "@Item", [Item]), [@Item] IN VALUES( 'Specific ITMCODE'[ITMCODE] ))
    TREATAS( SELECTCOLUMNS( nTable, "@Item", [Item]), 'Specific ITMCODE'[ITMCODE] )
) + [Item Revenue]

Here’s your sample file. sample table.pbix (41.9 KB)
I hope this is helpful.

MudassirAli · November 6, 2020, 2:18pm

@Melissa I had been trying to get it working through virtual tables and been scratching my head all along. It’s weird that Calculated Columns are working but not vTables.

Great work on this one

AntrikshSharma · November 6, 2020, 4:31pm

@Melissa Here is a DAX Solution, just don’t sue me for plagiarism

Antriksh = 
VAR OriginalData =
    ALL ( 'Sample Table' )
VAR ItemList =
    FILTER (
        ALL ( 'Sample Table'[ITMCODE] ),
        CONTAINSSTRING ( 'Sample Table'[ITMCODE], "A" )
    )
VAR LimitColumns =
    ALLEXCEPT ( 'Sample Table', 'Sample Table'[AMOUNT] )
VAR FilterItems =
    FILTER ( LimitColumns, 'Sample Table'[ITMCODE] IN ItemList )
VAR Count_ITMCODE =
    ADDCOLUMNS (
        FilterItems,
        "Count",
            VAR CurrentCode = 'Sample Table'[SLIP]
            VAR SameRows =
                FILTER ( FilterItems, 'Sample Table'[SLIP] = CurrentCode )
            VAR Result =
                COUNTROWS ( SameRows )
            RETURN
                Result
    )
VAR FinalTable =
    ADDCOLUMNS (
        Count_ITMCODE,
        "Amount w/o ITMCODE",
            VAR CurrentSlip = 'Sample Table'[SLIP]
            VAR FilterRows =
                FILTER (
                    OriginalData,
                    'Sample Table'[SLIP] = CurrentSlip
                        && NOT 'Sample Table'[ITMCODE] IN ItemList
                )
            VAR Result =
                SUMX ( FilterRows, 'Sample Table'[AMOUNT] )
            RETURN
                Result,
        "Amount with ITMCODE",
            VAR CurrentSlip = 'Sample Table'[SLIP]
            VAR CurrentCode = 'Sample Table'[ITMCODE]
            VAR FilterRows =
                FILTER (
                    OriginalData,
                    'Sample Table'[SLIP] = CurrentSlip
                        && 'Sample Table'[ITMCODE] = CurrentCode
                )
            VAR Result =
                SUMX ( FilterRows, 'Sample Table'[AMOUNT] )
            RETURN
                Result
    )
VAR PrelimResult =
    SELECTCOLUMNS (
        ADDCOLUMNS (
            FinalTable,
            "Final",
                [Amount w/o ITMCODE] / [Count] + [Amount with ITMCODE]
        ),
        "Slip", 'Sample Table'[SLIP],
        "ITMCODE", 'Sample Table'[ITMCODE],
        "Slip Revenue", [Final]
    )
VAR Result =
    SUMX (
        FILTER ( PrelimResult, [ITMCODE] IN VALUES ( 'Sample Table'[ITMCODE] ) ),
        [Slip Revenue]
    )
RETURN
    Result

sample table.pbix (50.3 KB)

Melissa · November 6, 2020, 4:39pm

Thanks @AntrikshSharma !!!

Knew I was missing something and I hoped someone would jump in to take another look. Thanks for putting this to bed otherwise I wouldn’t get any sleep…

vernonwyng · November 7, 2020, 12:48pm

@AntrikshSharma, @Melissa, @MudassirAli
Thank you all for helping out and contributing to the solution.

AntrikshSharma · November 7, 2020, 6:33pm

Made a slight modification to the code to optimize it, and covered a loophole that I missed earlier i.e. using ALLEXCEPT and forgetting that it will return unique rows in case of duplicates, which is not good for this example.

sample table updated.pbix (49.7 KB)
back-end query changed from this:

to this:

Removed FE calls i.e. CALLBACKDATAID by changing

VAR FilterItems =
    FILTER ( OriginalData, 'Sample Table'[ITMCODE] IN ItemList )

to this:

VAR FilterItems =
    CALCULATETABLE (
       'Sample Table',
       'Sample Table'[ITMCODE] IN ItemList,
        REMOVEFILTERS ( 'Sample Table' )
    )