Help subtracting a measure from column

[Anonymous]

Hi everyone,

I am having trouble subtracting a measure from a column in directquery mode. 

I am trying to subtract Column 3 from TaktDiff which is a measure. The issue is that TaktDiff is a summation of values based on Column Serial. Column 3 is determined by an if statement in a separate table that is based on the values in Column Tier 2. Since one is a measure and the other is a calculated column, I cannot figure out a way to find the difference between them and still preserve the summation of TaktDiff.

I have been working on this for a couple hours now and I am pretty stuck. I am sure I am missing something obvious.

Any help would be appreciated.

[S184019]

I am trying to build a table like the one below: 

SalesPersonID	RegionID	Week	Baseline Sales	Sales	Variance	Running Variance
1	1	6/27/20	45,000	45,128	128	128
1	1	7/4/20	45,000	44,872	-128	0
1	1	7/11/20	45,000	46,194	1,194	1,194
1	2	6/27/20	45,000	49,220	4,220	5414
2	1	6/27/20	38,000	38158	158	158

I am unsure how to go about calculating the running variance.  The assumption is, I can just take the baseline sales and subtract the sales and that gives us the variance, but then adding that variance doesn't work when we get to the next region or the next sales person.  

I have built a summary table to calculate the baseline sales which is the average sales value in a given time frame before 5/30/20 regardless of the region. 

I am able to calculate a variance, but I need to be able to calculate that running variance by salesperson.  It isn't as simple as;

Running Variance = SUMX(SUMMARIZE('table1','table1'[salespersonID],

"_1",CALCULATE(SUM('table1'[Sales]),'table1'[Baseline Vol Flag]=1), -- This part creates the baseline 

"_2",CALCULATE(SUM('table1'[Sales]),'table1'[Baseline Vol Flag]=1) - SUM('table1'[Sales]), -- This calculates the variance

[_1]-[_2])

Notice how the [_2] doesn't take into consideration the previous variance. 

Ideally what I would like is Sales - Baseline + Previous Variance = Running Variance

Any help?

[Mariusz]

Hi @Anonymous 

Try using this

Measure = 
SUMX( 'Table', [TaktDiff] - 'Table'[Column 3] )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

@Mariusz

This one doesn't quite work. It calculates based on the non-aggregated before they are summed into TaktDiff. 

[Mariusz]

Hi @Anonymous 

Try with group by

Measure = 
SUMX( 
    GROUPBY( 
        'Table', 
        'Table'[Serial], 
        'Table'[Tier2], 
        "@Column 3", SUMX( CURRENTGROUP(), 'Table'[Column 3] ) ),
    [TaktDiff] - [@Column 3] 
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Measure = 
SUMX( 
    GROUPBY( 
        'Table', 
        'Table'[Serial], 
        'Table'[Tier2], 
        "@Column 3", SUMX( CURRENTGROUP(), 'Table'[Column 3] ) ),
    [TaktDiff] - [@Column 3] 
)

This seems to have the same issues as the previous SUMX function. Although, I am not sure why. The issue seems to be from previously calculating the SUM of TakDiff based on Serial.

[parry2k · Accepted solution]

@Anonymous you have to be specific which solution you tried and not worked, you have few awesome people replied to your post, be more specific. 

[Anonymous]

Sorry, I tried to tag the solutions as I was trying them. 

Looks like your solution 

Diff = [Your Measure] - SUM ( Table[Column3] )

works if you swap the SUM out with MIN. Using the sum function seems to give me the same issue as using the SUMX function in Mariusz's solution. I am not 100% sure why though.

Either way, thanks for the help.

[parry2k]

@Anonymous since you already know that issue is that you cannot subtract column from a measure means you need to convert your column to a measure and in this case, you can use aggregation methods like MIN or MAX or SUM, try this. I would use a SUM function

Diff = [Your Measure] - SUM ( Table[Column3] )

[amitchandak]

Not sure I got it, Try like

sumx(summarize(Table,Table[Serial], Table[Tier], "_1",sum(Table[TaktDiff]), "_2",sum(Table[Column3])),[_1] -[_2])