Forum Discussion
How to make mesaure work on multiple lines or have columns react to filters?
Hi you more experienced Power BI users
I can't get a measure to calculate my values correct so if someone could explain what I am doing wrong that would be much appriciated. I have created a very simple testcase - don't look like I can attache that file to this message thou... I'll try to display what I have in the text and pictures.
I have 5 tables.
Product, Customers and Campaigns
Then two relational tables connecting the Campaigns with Customers and Products.
In the Campaign table I have a measure that calculate the max score that is Customers x Products. This measure works fine on a row level but as you can see above it fails when looking at more than one campaign at a time.
MeasureTotScore = Campaigns[CountCustomers]*Campaigns[CountProduct]
CountCustomers = COUNTROWS('RelationCampCust')
CountProduct = COUNTROWS('RelationCampProd')
If I instead create a Column with the same formula it works fine for one or more Campaigns but then the values dont react to Filter changes. Again looking at the picture - if I select Product type A I want that to reflect in my numbers. This is what I get:
Again on a single row the measure does it job but completely fails when looking on multiple lines. And the calculated column isnt affected at al of my selection. The result I want is 12 in my example above.
Any pointers for a newbi?
Regards,
Thomas
Hi TCR_Carlsberg,
In my test, I named the three tables as 'Products', 'Customers' and 'Campaigns'. Please try below measures:
CountCustomers = DISTINCTCOUNT(Campaigns[CustID]) CountProduct = DISTINCTCOUNT(Campaigns[ProdID]) MeasureTotScore = SUMX(Campaigns, Campaigns[CountCustomers]*Campaigns[CountProduct])
Best regards,
Yuliana Gu
1 Reply
- v-yulgu-msft
Microsoft Employee
Hi TCR_Carlsberg,
In my test, I named the three tables as 'Products', 'Customers' and 'Campaigns'. Please try below measures:
CountCustomers = DISTINCTCOUNT(Campaigns[CustID]) CountProduct = DISTINCTCOUNT(Campaigns[ProdID]) MeasureTotScore = SUMX(Campaigns, Campaigns[CountCustomers]*Campaigns[CountProduct])
Best regards,
Yuliana Gu