Forum Discussion

LindaHa's avatar
LindaHa
Icon for Helper I rankHelper I
10 years ago

Per 1000 per month

Hello,
 
I am trying something simular. I have a list with classifications but I need those per month per 1000 sectors . The per month separation works fine but I do not know how to get in the per 1000. In Excel I was able to do it with = (number of classification)*1000/(real number of sectors) but when I use this in BI the result is empty.
 
 In fact I want to show my amount of classification per month per 1000 sectors in a graph.
 
Thanks for any assistance.
 

Need Help

12 Replies

    • LindaHa's avatar
      LindaHa
      Icon for Helper I rankHelper I
      10 years ago

      This is my graph. I need exactly this but not with absolute numbers but with "amount of classification per month per ttl flights".

       

       

      current graphRaw dataper sector values

      • LindaHa's avatar
        LindaHa
        Icon for Helper I rankHelper I
        10 years ago

        so the x-axis should not be the real numbers but the "classification per 1000 flights".

  • AlexChen's avatar
    AlexChen
    Icon for Microsoft Employee rankMicrosoft Employee
    10 years ago

    Hi,

     

    I assume your first table called “accident” and the second table called “flights”.

     

    I changed the records in these 2 table in order to give an example.

     

     

     

    First,  You need to build a relationship between accident[month] and flights[Month].

     

    Now You can add a calculated column to calculate the value:

     

    Column = DIVIDE(CALCULATE(COUNTA(accident[classification]), RELATEDTABLE(accident)) * 1000, [TTL])

     

    This is the result:

     

     

     

    Best Regards

    Alex

     

    • LindaHa's avatar
      LindaHa
      Icon for Helper I rankHelper I
      10 years ago

      Hi Alex,

       

      thanks for this! Could you please explain to me why I have to add an ID?

       

      Thanks Linda

      • AlexChen's avatar
        AlexChen
        Icon for Microsoft Employee rankMicrosoft Employee
        10 years ago

        Hi LindaHa,

         

        I add an ID just to give a example to show the records clearly. You don't have to add it.

         

        Best Regards

        Alex