Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

/ 30 (days)

Hi All,    This is my current formula (see the top formula below), but it does not work correctly because some days there is no activity so I need to tell PowerBI to include the empty cells as 0. ...
DAX
  • SpartaBI's avatar
    SpartaBI
    4 years ago

    Add another ) after 0 ) so you will have there 0 )),

  • Anonymous's avatar
    Anonymous
    4 years ago
    Average Durations =
    CALCULATE (
    AVERAGEX (
        VALUES ( 'Calendar'[Date] ),
           COALESCE ( [SDR ID average per CP ID], 0 )),
    'FACT TABLE'[Charger Type] = 'Active'
     
    That does look good, the problem is that I am getting an error: 'Cannot find table 'Active'. 
    I do not have a table with Active/Non Active column. 
    Is there a different way of doing this. My line graph is showing "0" according to the calendar before the item started operating and runs into the future months (I guess as per the calendar). 
  • SpartaBI's avatar
    SpartaBI
    4 years ago

    Well, that is because you replace blank with 0 in your original measure. 

    So forget what we did, it's not related to your issue.

    Why did you replace the blank with 0 inside the coalesce? You have blanks inside the range?

    Anyway, if you share the file and what you need I could solve this quickly. Will need to continue later, going away from PC, so will ping here later tonight. Until then please try to share the file or a sample of it.

    P.S. appreciate your kudos

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi, 

 

WIth regards to myt message earlier. I've tested it and in theory the "+ 0" and the "coalesce" formula work.
The issue is that I get the average by the Connector, not by the Charger Type. 
I use my formula:

SDR ID average per CP ID avgerage per Day(coalesce) =
AVERAGEX (
    VALUES ( 'Calendar'[Date] ),
    COALESCE ( [SDR ID average per CP ID], 0 )
)
 
It works well with my Connectors but it doesn't average the 3 Connectors onto one 'Charger Type'
See the tables:

The charger type should give me 0.59, instead it sums up the three connectors (1.77)
What can I do?
Please assist, and thank you in advance. 
Best, 

J

  • SpartaBI's avatar
    SpartaBI
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Anonymous hey, can you share a sample file maybe?