Measure Subtotal Not Showing sum of rows, HASONEVALUE workaround does not fix

[Anonymous]

I am attempting to create a measure that is essentially a weighted average of (Growth Rate %) * (Volume Change) over a given time period.

e.g. we have a marketing campaign that is targeted towards small businesses. That particular campaign grew at 40% over the last 4 months, and it grew at 400 total opportunities over the same time period. The proposed metric would thus be (40% growth * 400 total opps).

When I try to create the measure, I am getting the "correct" and intended results in each row/campaign. But the subtotal of this column is not calculating the way I want it to, as it is not summing the results of each row. Rather, it is calculating the result off the subtotal row, producing a different #..

I have tried to solve this issue using the most common workaround, the "hasonevalue" solution below, but it has not solved my issue as it is returning nothing but 0's in the SUMX portion of the first variable.

Impact Factor (%) = 
VAR ImpactFactor = sumx(('MIS - Netezza Data V2'), abs([Max Month Opp Count] - [Min Month Opp Count]) * (abs([Max Month Conversion Rate] - [Min Month Conversion Rate])))
VAR ImpactTotal = summarize('MIS - Netezza Data V2','MIS - Netezza Data V2'[MQL_CAMPAIGNNAME],"Sum Impact", ImpactFactor)

RETURN
IF( HASONEVALUE( 'MIS - Netezza Data V2'[MQL_CAMPAIGNNAME] ) , 
ImpactFactor,
SUMX(ImpactTotal, [Sum Impact] ) )

[v-shex-msft]

HI @Anonymous ,

Can you please share some sample data for test? It is hard to test your formula without any detailed data.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[Anonymous]

@v-shex-msft 

Xiaoxin, Thank you for asking for sample data. I have attached some screenshots as I cannot link to my company's OneDrive. 

You'll notice in the screenshot below that the issue is occurring in the "Impact Factor - Arbitrary #" column where the subtotal issue is occurring and in the column to the right, where I am unable to get a result other than 0.

Impact Factor - Arbitrary # = calculate((([(Max - Min) Month Opp Count Difference])  * [(Max - Min) Month Conversion Rate (%) Difference]))

Impact Factor (%) = 
VAR ImpactFactor = sumx(('Database Fields'), abs([Max Month Opp Count] - [Min Month Opp Count]) * (abs([Max Month Conversion Rate] - [Min Month Conversion Rate])))
VAR ImpactTotal = summarize('Database Fields','Database Fields'[MQL_ID],"Sum Impact", ImpactFactor)

RETURN
IF( HASONEVALUE( 'Database Fields'[MQL_ID] ) , 
ImpactFactor,
SUMX(ImpactTotal, [Sum Impact] ) )

[v-shex-msft]

HI @Anonymous ,

In fact, this snapshot not help, I can only find the matrix with category and measure results.
Can you please share some dummy data with minimum table structure for test? (you can upload to google drive or direct past them as table format to your post)

In addition, it seems like you nested many fields and measures, they will cause filter conflict and performance issues:

Optimizing DAX expressions involving multiple measures 

Regards,

Xiaoxin Sheng

[Anonymous]

I cannot attach a test .PBIX file as I do not have permission to share from our corporate onedrive.

I have attached a table with some sample data instead. The subtotal of the sample data is not calculating the way I want it to, as it is not summing the results of each row. Rather, it is calculating the result off the subtotal row, producing a different #.

The main issue is that the impact factor (%) equation returns all 0's.

Impact Factor (%) = 
VAR ImpactFactor = sumx(('MIS - Netezza Data V2'), abs([Max Month Opp Count] - [Min Month Opp Count]) * (abs([Max Month Conversion Rate] - [Min Month Conversion Rate])))
VAR ImpactTotal = summarize('MIS - Netezza Data V2','MIS - Netezza Data V2'[MQL_CAMPAIGNNAME],"Sum Impact", ImpactFactor)

RETURN
IF( HASONEVALUE( 'MIS - Netezza Data V2'[MQL_CAMPAIGNNAME] ) , 
ImpactFactor,
SUMX(ImpactTotal, [Sum Impact] ) )

Impact Factor - Arbitrary # = calculate((([(Max - Min) Month Opp Count Difference])  * [(Max - Min) Month Conversion Rate (%) Difference]))

Sample data

MQL_CAMPAIGN	(Max - Min) Month Opp Count Difference	Impact Factor - Arbitrary #	(Max - Min) Month Conversion Rate (%) Difference	Impact Factor (%)
70170000000b5F2AAI	-447	163	-36.40%	0.00%
7010g000001UKYeAAO	-291	109	-37.30%	0.00%
7010g000001Q4ciAAC	-148	67	-45.00%	0.00%
7010g000000mgA3AAI	-183	66	-36.00%	0.00%
7010g000000mhi5AAA	-73	16	-21.30%	0.00%
70170000000dKndAAE	-41	14	-33.30%	0.00%
70170000000VHR1AAO	-37	13	-34.80%	0.00%
7010g000000mgPlAAI	11	7	60.70%	0.00%
TOTAL (all rows)	-1364	122	-9%	0.00%

[v-shex-msft]

HI @Anonymous ,

For fields [Max Month Opp Count], [Min Month Opp Count], [Max Month Conversion Rate], [Min Month Conversion Rate] which existed in your formula, are these fields column or measures?

If you mean column, you can try to use below formula if it works:

Impact Factor (%) =
VAR ImpactTotal =
    SUMMARIZE (
        'Table',
        'Table'[MQL_CAMPAIGNNAME],
        "Sum Impact", ABS ( 'Table'[Max Month Opp Count] - 'Table'[Min Month Opp Count] )
            * ABS ( 'Table'[Max Month Conversion Rate] - 'Table'[Min Month Conversion Rate] )
    )
RETURN
    SUMX ( ImpactTotal, [Sum Impact] )

If not, please share these measure formulas for further tests.

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @v-shex-msft 

Thank you for the response!

1. Max Month Opp Count
2. Min Month Opp Count
3. Max Month Conversion Rate
4. Min Month Conversion Rate

These 4 are all measures. 

Max Month Opp Count = 
VAR MAXMONTH = [Max Month]
VAR MAXYEAR = [Max Year]
RETURN(
    CALCULATE([Opportunity - Total Count (Netezza)]  ,
    'MIS - Fiscal Calendar'[MONTHINYEAR] = MAXMONTH,
    'MIS - Fiscal Calendar'[FISCALYEAR] = MAXYEAR))

Min Month Opp Count = 
VAR MINMONTH = [Min Month]
VAR MINYEAR = [Min Year]
RETURN(
    CALCULATE([Opportunity - Total Count (Netezza)],
    'MIS - Fiscal Calendar'[MONTHINYEAR] = MINMONTH,
    'MIS - Fiscal Calendar'[FISCALYEAR] = MINYEAR))

Max Month Conversion Rate = 
VAR MAXMONTH = [Max Month]
VAR MAXYEAR = [Max Year]
RETURN(
    CALCULATE([Conversion Rate (%) - MQL to Opportunity By MQL Create Date],
    'MIS - Fiscal Calendar'[MONTHINYEAR] = MAXMONTH,
    'MIS - Fiscal Calendar'[FISCALYEAR] = MAXYEAR))

Min Month Conversion Rate = 
VAR MINMONTH = [Min Month]
VAR MINYEAR = [Min Year]
RETURN(
    CALCULATE([Conversion Rate (%) - MQL to Opportunity By MQL Create Date],
    'MIS - Fiscal Calendar'[MONTHINYEAR] = MINMONTH,
    'MIS - Fiscal Calendar'[FISCALYEAR] = MINYEAR))

OTHER POTENTIALLY USEFUL MEASURES THAT ARE NESTED IN THE ABOVE MEASURES:

Max Month = calculate(max('MIS - Fiscal Calendar'[MONTHINYEAR]))

Max Year = calculate(max('MIS - Fiscal Calendar'[FISCALYEAR]))

Conversion Rate (%) - MQL to Opportunity By MQL Create Date = ([Opportunity - Total Count (Opp Table)]/[MQL - Total Count])

[v-shex-msft]

HI @Anonymous ,

Actually, power bi calculated on total level(whole table records) instead of direct summary sublevel results. (summary on the whole table instead apply multiple aggregates on calculation result)

Since this formula is calculated on the total table level, so it gets the result min and max date from whole table records, it obviously will get a bigger result than row contents level. (e.g. each row are calculated in the same year, but for the table level, its will calculate across multiple years)

The common solution is: add a variable summarize table and try to limit the calculation of each measure on the current row level, then use iteration function to apply second aggregate calculation on summarize table to get a total value of previous calculation result.

Measure Totals, The Final Word  

I'm not so sure why it does not work on your side, maybe a pbix file with minimum table structure and some dummy data will help us to test and troubleshoot on it.
Regards,

Xiaoxin Sheng

[Anonymous]

Hi, @v-shex-msft 

I cannot upload a pbix file with minimum table structure as my company does not permit this. I tried to add a variable summarize table using the link you provided, but the issue still persists.

[Anonymous · Accepted solution]

@v-shex-msft I am not sure why, but it turns out that this particular suggestion solved my issue 

https://powerpivotpro.com/2012/03/subtotals-and-grand-totals-that-add-up-correctly/

Impact Factor - Arbitrary # test = 
if( countrows( values('MIS - Netezza Data V2'[MQL_CAMPAIGNNAME] ) ) = 1,
[Impact Factor - Arbitrary #],
sumx(VALUES('MIS - Netezza Data V2'[MQL_CAMPAIGNNAME] ), [Impact Factor - Arbitrary #] ) 
)