topic % of days where value is 0 in DAX Commands and Tips

% of days where value is 0

OZEEE — Tue, 03 Sep 2019 16:56:04 GMT

I am trying to calculate the % of days where the value is 0. Below is sample data. Underlying dataset has multiple values each day. I need to know if the total of all values for a day is above 0. Then what percent of days are above 0

Re: % of days where value is 0

Nathaniel_C — Tue, 03 Sep 2019 18:36:53 GMT

OZEEE ,

Try this... If you use the numerator as another measure, it will give you the count.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Re: % of days where value is 0

OZEEE — Tue, 03 Sep 2019 19:00:11 GMT

Hi Nathan,

The problem with this is that each row isn't a distinct date. For example, I could have two values on 6/1/2018, but I still need that to count as 1 "non-zero day". The dates with more than than one value will distort the calculation I am looking for. I guess another way to word it is, "What percent of the time is the sum of a day 0 throughout the year"

Any thoughts? The denominator should be 365 when analyzing a year's worth of data

Re: % of days where value is 0

Nathaniel_C — Tue, 03 Sep 2019 19:27:46 GMT

Hi OZEEE ,

Tell me more. On 6/1/2018 you say that you can have two values. Could they both be zero, could one be zero and the other not? What happens then?

Nathaniel

Re: % of days where value is 0

Nathaniel_C — Tue, 03 Sep 2019 22:28:06 GMT

Hi OZEEE ,

Rather than assuming 365, you can use this measure to count the unique entries in your date column for the denominator.

Distinct Values = COUNTROWS(DISTINCT(AboveZ[DOS]))

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Re: % of days where value is 0

Nathaniel_C — Tue, 03 Sep 2019 23:14:07 GMT

Hi OZEEE

See this picture, at the left is my data table, with 10 rows, and June 2 has a report of 0 and 2. There are actually 8 distinct dates, so my measure [Distinct Values] gives you the denominator. Then for the numerator, I used SUMMARIZE() to group by date, so we lost one day = 0. Use the measure [Summarize] for the numerator. I put the measure [Percent of Days 2] in the editing bar at the top. ([The Percent of Days] is superseded by the [Percent of Days 2].)

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel