cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jiCAT
Helper IV
Helper IV

If IS BLANK

Hi, 
 
I have this formula:
 
SDR ID average per CP ID average per Day =
AVERAGEX(
VALUES('FACT TABLE'[Start Date]),
CALCULATE([SDR ID average per CP ID])
)
 
It doesn't calcultates the averages properly becasue I have noticed that there are gaps in the data. 
I think I need to add IF IS BLANK function as "0" but not sure how to  do this. 
jiCAT_0-1660206673419.png

Any suggestions, advise woulld be very much apreciated. 
Thank you,
J

1 ACCEPTED SOLUTION

I don't know the visual, therefore it's hard for me to troubleshoot, especially that I don't have access to the data.

 

But you can try to do it the right way, which means not using the fact table to slice and dice. Instead, use the dimensions only on your visuals as it should be. And change the measure to:

SDR ID average per CP ID average per Day =
AVERAGEX(
    VALUES( 'Dates'[Date] ),    
    [SDR ID average per CP ID] + 0
)

You should never put any colums from your fact table on the canvas. There's only one case where this is admissible: if you have a degenerate dimension stored in the fact table. But I don't think you do as it's a very rare occurence.

 

If the above does not work, then I'm afraid you'll have to make your file available (maybe with fake data but it should demonstrate the issue, obviously).

View solution in original post

13 REPLIES 13
johnt75
Super User
Super User

You don't need to explicitly call CALCULATE for a measure, that is done implicitly whenever a measure is called.

Rather then IF ISBLANK you can use COALESCE, like 

SDR ID average per CP ID average per Day =
AVERAGEX (
    VALUES ( 'FACT TABLE'[Start Date] ),
    COALESCE ( [SDR ID average per CP ID], 0 )
)

 

Hi johnt75, 
Thank you very much for your advise, I would think that would solve it all but it didn't. 
If you look at the visualisation, the outside circless calculate the averages, but the inner circle instead of averaginging the three it sums them up? 
Any idea how to fix this? 

Capture.PNG

Sorry, I'm not familiar with that visual so I don't know what its doing or what settings you may be able to change

Hi johnt75, 

 

The visual is irrelevant. I have also used one of the standard visualisations, and it does the same; For some reason I don't get the average of the total. 

Even using the Donut. The table has got the correct values, but when I hoover over Ultra-Rapid or Rapid the number that is showing is 4.34 for Ultra-Rapid and 2.47 for Rapid - these are not averages. It should be 2.61 and 1.44 respectfully. 
2.PNG

daXtreme
Super User
Super User

 

// Just so you know, you should never have a model
// with just one table. If you do, you'll be facing
// issues you will either 1) not be able to explain
// or 2) have issues you'll never be aware of (wrong
// calculations). Just a warning from an old timer.

// If you want to include blanks as well as 0's, it's
// enough to add 0 to your measure. Measures don't 
// need to be wrapped in CALCULATE since the engine
// does it automatically for you.

SDR ID average per CP ID average per Day =
AVERAGEX(
    VALUES( 'FACT TABLE'[Start Date] ),    
    [SDR ID average per CP ID] + 0
)

 

If you want to build correct (meaning: no hidden gotchas) models, please stick to these guidelines: Understand star schema and the importance for Power BI - Power BI (bing.com)

Hi daXtreme,

Thank you very much for your advise,; I do have additional tables: Calendar table and Dim table; For this problem I am using the Fact Table and my Calendar Table. 


I would think that your function would solve it all but it didn't. 

If you look at the visualisation, and using your, or my formula (receiving same results);

The outside circless calculate the averages, but the inner circle instead of averaginging the three, it sums them up instead. 
Any idea how to fix this? 

Capture.PNG

I don't know the visual, therefore it's hard for me to troubleshoot, especially that I don't have access to the data.

 

But you can try to do it the right way, which means not using the fact table to slice and dice. Instead, use the dimensions only on your visuals as it should be. And change the measure to:

SDR ID average per CP ID average per Day =
AVERAGEX(
    VALUES( 'Dates'[Date] ),    
    [SDR ID average per CP ID] + 0
)

You should never put any colums from your fact table on the canvas. There's only one case where this is admissible: if you have a degenerate dimension stored in the fact table. But I don't think you do as it's a very rare occurence.

 

If the above does not work, then I'm afraid you'll have to make your file available (maybe with fake data but it should demonstrate the issue, obviously).

Hi daXtreme, 

Very valuable tips! Thank you very much. I will treasure this. 

There is one last twing to my dashboard that needs to happen: 
with my function: 

SDR ID average per CP ID avgerage by Date =
AVERAGEX (
    VALUES ( 'Calendar'[Date] ),
       COALESCE ( [SDR ID average per CP ID], 0 )
)
PowerBI calculates all zeros, from when my callendar starts and running even to Q4 this year.
It doesn't consider when the item became active. How can I amend my function please?
Thanks a lot, 
J

Hi @jiCAT 

 

Sorry but I've no idea what you're talking about here... I'd need to see the data and have you explain the problem before my eyes. Not any sooner than that, I'm afraid 😞

Hi daXtreme, 
1.PNG

Average Sessions =

AVERAGEX (

    VALUES ( 'Calendar'[Date] ),

       COALESCE ( [SDR ID average per CP ID], 0 )

)

 

This is the formula that sort of works. It returns the results correctly, averaging the usage data including the days when the unit wasn’t in use, but the issue is that My calendar starts in 2020 so the formula calculates the Averages from 2020 not when my Connector was first used and runs to Q4 2022, as per my calendar.
I just need the function to calculate all the O’s between the 1st use until last usage, e.g. today.

With this function I am calculating the SDR ID(unique number) Average per CP ID (unit number) Average, per day;
Some days the items are not in use, so that’s why I thought I need to add the coalesce calculation.


I am filtering the above function by the column (FACT TABLE[Connector]).


I hope this makes sense. Please advise what can be done about this?
Thank you kindly.

@jiCAT 

 

When I said I needed to see the data, I was referring to the raw data underlying the model, not any visuals since the visuals don't have all the information that's required.

Hi daXtreme, 
This is a sample of the FACT DATA TABLE. I hope you can help. 

SDR IDCP IDConnectorConnector TypeDurationStart DateStart TimeEnd DateEnd Time
4056617 M0005ChaDeMoRapid00:12:1501/06/202200:08:0001/06/202200:20:00
4056684 M0019ChaDeMoRapid00:13:5101/06/202200:03:3701/06/202200:17:28
4056686A52104ChaDeMoRapid00:04:3331/05/202223:59:5201/06/202200:04:25
405663854008ewCCSRapid00:19:5931/05/202223:19:4031/05/202223:39:39
4056637530344CCSRapid00:49:3931/05/202223:17:5701/06/202200:07:36
40566355149823ChaDeMoRapid00:24:4531/05/202223:16:4431/05/202223:41:29
4056633346784gChaDeMoRapid00:40:3831/05/202223:15:5031/05/202223:56:28
40566295370133CCSRapid00:55:4731/05/202223:13:5101/06/202200:09:38
4056628600221CCSRapid00:59:1831/05/202223:13:2201/06/202200:12:40
4056627600351ACRapid01:02:5831/05/202223:12:0801/06/202200:15:06
4056626600425CCSRapid00:54:5431/05/202223:11:4501/06/202200:06:39
40566234350969ChaDeMoRapid00:38:4231/05/202223:11:3831/05/202223:50:20
40566221251860CCSRapid00:58:4031/05/202223:10:3801/06/202200:09:18
4056609521824ChaDeMoRapid00:58:3631/05/202223:02:2201/06/202200:00:58
4056607515684CCSRapid01:24:3231/05/202222:59:3201/06/202200:24:04
4056604541561CCSRapid00:20:3631/05/202222:58:0131/05/202223:18:37

2.PNG

My formula works great except it includes days/month/years when the item wasn't operational, so when I add my line graph, it shows me zeros from Jan 2020 where it should only show from Q1 2022 in my example. 

Thank you,

J

 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors