Count or SUM or something else is wrong

[Anonymous]

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

[v-jingzhang · Accepted solution]

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.

[amitchandak]

@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.

[AllisonKennedy]

Please can you share model view with relationships? What column connects the two raw tables? This looks like an issue with the relationships.

[Anonymous]

@AllisonKennedy 

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

[AllisonKennedy]

@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