Forum Discussion
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
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.
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.
5 Replies
- Please can you share model view with relationships? What column connects the two raw tables? This looks like an issue with the relationships.
AllisonKennedy
The Complaint rate is connected with Q2 Nummer should I connect WE Buchungen?- 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
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.
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.
