topic Re: If a row is zero the total of the row should be zero in DAX Commands and Tips

If a row is zero the total of the row should be zero

TeresaV — Wed, 30 Jun 2021 09:10:12 GMT

Hi,

I am trying to create a dax formula that shoud do this :

	Jan	Fev	March	April	May	June	July	August	September	Nov	Dec
CountryA	1	0	21	3654	65	3	13	0	0	0	0
CountryB	34	58	11	645	435	4	55	4	56	5	0
CountryC	78	75	41	0	3	665	541	4	9	3	1
CountryD	0	23	4	55	684	965	654	74	55	12	1



	Jan	Fev	March	April	May	June	July	August	September	Nov	Dec
Region A (Country A+B)	35	0	32	4299	500	7	67	0	0	0	0
Region B(Country C+D)	0	98	45	0	687	1620	1195	78	64	15	2

How can I replicate this?

amitchandak parry2k v-alq-msft

Re: If a row is zero the total of the row should be zero

Jihwan_Kim — Wed, 30 Jun 2021 11:52:06 GMT

https://www.dropbox.com/s/fv0amwpjj12o9lz/teresav.pbix?dl=0

Re: If a row is zero the total of the row should be zero

parry2k — Wed, 30 Jun 2021 15:32:58 GMT

TeresaV I have further enhanced Jihwan_Kim file (new pbix file is attached) and the Total shows up correctly.

here is the image:

and measure used to fix the total:

Result = SUMX ( SUMMARIZE ( Data, Months[Month], Countries[Region], "@Result", VAR _valuelist = VALUES ( Data[Value] ) RETURN IF ( 0 in _valuelist, 0, CALCULATE ( SUM ( Data[Value] ) ) ) ), [@Result] )

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

Re: If a row is zero the total of the row should be zero

TeresaV — Thu, 01 Jul 2021 15:14:49 GMT

What if I have another column , named source:

and I want to filter for sorce A only??

parry2k Jihwan_Kim

Re: If a row is zero the total of the row should be zero

parry2k — Thu, 01 Jul 2021 15:26:58 GMT

TeresaV not sure what you mean? Can you be clear on your requirements in one go? Sorry, but it is hard to answer if you are going to change the question.

Re: If a row is zero the total of the row should be zero

TeresaV — Thu, 01 Jul 2021 15:33:12 GMT

sorry , it is the same problem but with another column to filter.

Basically I want to achieve the same result as before :

but instead of having just the value I want to filter the value per source... if it is 0 in country A and 25 in country B (Country A+ country B= region A) then region A will be 0.

did you understood?