Forum Discussion
Help subtracting a measure from column
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.
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.
9 Replies
- amitchandak
Super User
Not sure I got it, Try like
sumx(summarize(Table,Table[Serial], Table[Tier], "_1",sum(Table[TaktDiff]), "_2",sum(Table[Column3])),[_1] -[_2])
- 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] ) This one doesn't quite work. It calculates based on the non-aggregated before they are summed into TaktDiff.
- Mariusz
Community Champion
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
- S184019
Advocate III
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 VarianceAny help?
