Forum Discussion

TCR_Carlsberg's avatar
TCR_Carlsberg
Frequent Visitor
8 years ago
Solved

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's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft 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