Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Count or SUM or something else is wrong

Hi Power BI Community! 
I am very frustraded about this simple thing.


So I just want to 

Complaint Rate = DIVIDE(Count('Stammdaten (3)'[Q2Nummer]);'WE Buchungen'[Summe WE Buchungen]).


So for May Q2 Nummer 8 / WE Buchungen 531 * 100 = 1,5 %
June: 20 / 585 * 100 = 3,4 % 
etc.

But I does not work with Count or Sum to get both of the values seperate in three months. I also have a relationship between Q2 Nummer and WE Buchungen.


I count on you
Need Help

5 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    5 years ago
    Please can you share model view with relationships? What column connects the two raw tables? This looks like an issue with the relationships.
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      AllisonKennedy 

      The Complaint rate is connected with Q2 Nummer should I connect WE Buchungen?

      • AllisonKennedy's avatar
        AllisonKennedy
        Icon for Community Champion rankCommunity Champion
        5 years ago
        Anonymous
        You can't connect two tables using a measure, so the WE Buchungen table must be related to Stammdaten table by some column - when you hover over the line between the tables you can see which column. Without knowing anything about your data, I can't say which columns would be the right ones to use for this relationship, but you can't directly sum the columns from WE Buchungen table in 'values' in a visual because of your cross filter direction.
        Try adding a new calculated column to the Stammdaten table
        WE Buchungen = RELATED('WE Buchungen'[WE Buchungen (MNR)] )

        Then use that new column in your DIVIDE measure.

        Also see this post I wrote on Cross Filter direction and see if it helps explain why it's not working currently: https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power_92.html
  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , First of you date tables seem to have less dates than you fact table.

     

    This formula is not clear

     

    This can

    divide( sum('WE Buchungen'[Summe WE Buchungen]), calculate(sum('WE Buchungen'[Summe WE Buchungen]),all('WE Buchungen')))

     

    On in case you want to divide by year total

    This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))

    This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))

     

    Have a separate Date Table for that.

     

     

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous 

     

    I’m not able to create an effective relationship between tables from the limited data in your post, so I add a date column to Stammdaten (3) table and use a date table to create relationships based on them. For the complaint rate, I create a measure like below and it works. 

     

    Complaint Rate = DIVIDE(SUM('Stammdaten (3)'[Q2 Nummer]),SUM('WE Buchungen'[WE Buchungen (MNR)]),BLANK())

     

    So you may consider using a date table to help build the relationships on.

     

    In addition, documentations below may be helpful:

    https://radacad.com/do-you-need-a-date-dimension

    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

    https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand

     

     

     

    Best Regards,

    Community Support Team _ Jing Zhang

    If this post helps, please consider Accept it as the solution to help other members find it.