topic How to sum when filter is based on the row in DAX Commands and Tips

How to sum when filter is based on the row

TomerIwanir — Wed, 07 Feb 2024 09:58:09 GMT

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 !

Re: How to sum when filter is based on the row

WinterMist — Wed, 07 Feb 2024 15:37:54 GMT

TomerIwanir

Please try the following.

Regards,

Nathan

Re: How to sum when filter is based on the row

TomerIwanir — Wed, 07 Feb 2024 15:59:23 GMT

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

Re: How to sum when filter is based on the row

WinterMist — Wed, 07 Feb 2024 16:45:47 GMT

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

Re: How to sum when filter is based on the row

TomerIwanir — Wed, 07 Feb 2024 18:35:00 GMT

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)

Re: How to sum when filter is based on the row

WinterMist — Wed, 07 Feb 2024 19:47:07 GMT

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

Re: How to sum when filter is based on the row

WinterMist — Wed, 07 Feb 2024 21:06:10 GMT

TomerIwanir

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

Re: How to sum when filter is based on the row

TomerIwanir — Thu, 08 Feb 2024 09:57:52 GMT

Tried to create the first measure

I got the following error, what am I missing ??

Re: How to sum when filter is based on the row

TomerIwanir — Thu, 08 Feb 2024 11:08:13 GMT

Im ok sharing it with u just not sure how to 🙂

but we are getting closer,

any idea whats wrong at row 3?

Re: How to sum when filter is based on the row

WinterMist — Thu, 08 Feb 2024 17:01:18 GMT

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.

Re: How to sum when filter is based on the row

TomerIwanir — Thu, 08 Feb 2024 20:00:04 GMT

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%

Re: How to sum when filter is based on the row

WinterMist — Wed, 14 Feb 2024 17:32:34 GMT

TomerIwanir

Would you be able to upload the PBIX to Google Drive, and then share the link here in this thread?

Regards,

Nathan