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. 

SDR ID average per CP ID average per Day =
AVERAGEX(
VALUES('Calendar'[Date]),
CALCULATE([SDR ID average per CP ID])
)
 
I have tried: 
SDR ID average per CP ID average per Day (0) =
AVERAGEX(
    VALUES( 'Calendar'[Date] ),    
    [SDR ID average per CP ID] + 0
)
 
and: 
SDR ID average per CP ID avgerage per Day(coalesce) =
AVERAGEX (
    VALUES ( 'Calendar'[Date] ),
    COALESCE ( [SDR ID average per CP ID], 0 )
)
 
but the results are still not working. 
I would like to try to rewrite the formula but using Month in the callendar and divided by 30)
That would be close enough, just not sure how to do this. 

I would appreciate any assistance on this. 
Thank you greatly in advance. 

Best, 
J
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

13 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi SpartaBI, 

    I think the previous issue is not that relevant now. 
    Could you please help me with this function instead?

    SDR ID average per CP ID avgerage per Day(coalesce) =
    AVERAGEX (
        VALUES ( 'Calendar'[Date] ),
        COALESCE ( [SDR ID average per CP ID], 0 )
    )

    This function calculates the 0 even if the charger type wasn't in the ground then and it calcultes data in Q4 this year. 
    How to tell the function to take only data when the charger is actualy active please?
    Thank you, 
    J
  • 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
      Community Champion
      4 years ago

      Anonymous hey, can you share a sample file maybe?

  • SpartaBI's avatar
    SpartaBI
    Community Champion
    4 years ago

    Anonymous wrap it with calculate like this:

    CALCULATE(

    [Your function],

    TableName[Charger] = 'Active'

    )

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I'm not that profficient in Dax: 

      SDR ID average per CP ID avgerage by Date2 =
      CALCULATE(
      AVERAGEX (
          VALUES ( 'Calendar'[Date] ),
             COALESCE ( [SDR ID average per CP ID], 0 ),
      [FACT TABLE(Charger Type]) = 'Active'
      )
      Please rearrange for me. This is obviously wrong. Thank you so much
      • SpartaBI's avatar
        SpartaBI
        Community Champion
        4 years ago

        Anonymousthe code is correct, just remove the [ before the FACT.

        If you don't get the right result then the question does the original measure works fine for all charger types?