# Total Revenue (in Groups)

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

and output them in a format as following:

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])))

sample table.pbix (28.7 KB)

Can you provide a sample pbix

attached sample pbix file, thanks.

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

@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

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

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.

@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

@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

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 =
ALLNOBLANKROW( 'Specific ITMCODE'[ITMCODE] ),
"@NewItemCode", [ITMCODE] &""
)
VAR vAllSlips =
VALUES( 'Sample Table'[SLIP] ),
"@Num of Items", CALCULATE( COUNTROWS( 'Sample Table' ), FILTER(  VALUES('Sample Table'[ITMCODE]), 'Sample Table'[ITMCODE] IN vAllItems ))
)
VAR vSlipItems =
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)

4 Likes

@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

@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 =
FilterItems,
"Count",
VAR CurrentCode = 'Sample Table'[SLIP]
VAR SameRows =
FILTER ( FilterItems, 'Sample Table'[SLIP] = CurrentCode )
VAR Result =
COUNTROWS ( SameRows )
RETURN
Result
)
VAR FinalTable =
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 (
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)

3 Likes

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ā¦

3 Likes

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

1 Like

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' )
)``````
1 Like