Forum Discussion
How to sum when filter is based on the row
Hi All,
I have a fact table that contain:
- amount of sales (DC)
- Department (product group)
- department&year&month (product group & FYM)
I want to create a formule similer to excel sum if that doing for each row:
=DC/ sumof DC according to the productroup&FYM
for eample, I want to get in the first row = 7286/(7286+4372+5829+7286)
for the seconf row = 4372/(7286+4372+5829+7286)
for the last row = 8473/(7286+4372+8743)
Thx !
11 Replies
- WinterMist
Impactful Individual
Hi, maybe im missing here something
at row 8 in your explanation
where:
productgroup&FYM is my original column
Var_group =
max(Invoices[Productgroup& FYM]))any idea why im getting this error?thx again
- WinterMist
1) You need to close the REMOVEFILTERS argument with a ")" after your 'Invoices' table.
INCORRECT: REMOVEFILTERS(Invoices, Invoices[Productgroup...))
CORRECT: REMOVEFILTERS(Invoices), Invoices[Productgroup...)
2) You need to check where Invoices[Productgroup& FYM] = _CurrentGroup (which is a VAR), rather than Invoices[VAR_group], which looks like a 'table'[column].
3) In the screenshot of your measure, it looks like you're just starting with the CALCULATE, rather than declaring the variables needed.
Please try to follow the same structure of the measure provided.
4 VARS, followed by a RETURN.
Regards,
Nathan
yes, I build the first 3 var that u meantioned as table (column),
not sure how to built VAR (im pretty new to this and im not working with SQL at all)
- WinterMist
OK, yeah. You don't want to create calculated columns in this case.
You just want to create 1 single measure, and add it to your table visual.
It's the same measure I shared originally.
The measure shows how to create VARs within a measure.
You simply use the keyword "VAR", followed by the variable name (any name you like), and then assign the variable whatever value or calculation.
Please create the measure shown (with your table/column references of course) and add it to the table visual.
Regards,
Nathan
Tried to create the first measure
I got the following error, what am I missing ??
- WinterMist
If it's still not working, please share the link to the PBIX and I should hopefully be able to get it working for you.
If that is not possible, then send me your code again, with results (or errors) as before.
Regards,
Nathan
Im ok sharing it with u just not sure how to 🙂
but we are getting closer,
any idea whats wrong at row 3?
- WinterMist
As mentioned previously....
1) You (still) need to close the REMOVEFILTERS argument with a ")" after your 'Invoices' table.
INCORRECT: REMOVEFILTERS(Invoices, Invoices[Productgroup...))
CORRECT: REMOVEFILTERS(Invoices), Invoices[Productgroup...)
2) You've also placed 2 close parenthesis back to back at the end of the CALCULATE. You only need 1.
Your code....
My code...
NOTE: When you use functions with multiple parameters, it is easier to read & avoid syntax errors if you separate each parameter onto its own line. Notice how in your code, you have the entire CALCULATE (with all 3 parameters) on one single code line. This can make it more difficult to see where parenthesis need to match up, as well as the separation of params. Notice how in my code, it's much easier to see these things, simply because the params are each separated onto their own lines.
Regards,
Nathan
P.S. If this does not resolve it for you, you can upload the PBIX to Google Drive, and then share the link here in this thread.
So the formula is finally worked but something is odd with the numbers, its suppose its need to sum into 100% every customergroup & FYM, but as seen below, its not, did I did something wrong ? it got uo tp 85%
- WinterMist
Would you be able to upload the PBIX to Google Drive, and then share the link here in this thread?
Regards,
Nathan
