Forum Discussion

NaderSaeed's avatar
NaderSaeed
Helper II
6 years ago
Solved

Smart Count If

Hi, I'll just go ahead with an example of what I need to do to illustrate what I want.   Line ID Line's Constant 1 0.23 2 0.15 3 0.325 4 0.14 5 0.314   Line ID Sto...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    NaderSaeed 

    Sheet7 is the table with constant, Shee8 is the table with stop reason.

     

    Result = 
var count_= CALCULATE(COUNTROWS(Sheet8),ALLEXCEPT(Sheet8,Sheet8[Line ID],Sheet8[Stop Reason]))
var constant =  CALCULATE(MAX(Sheet7[Line's Constant]),FILTER(Sheet8,Sheet8[Line ID]=RELATED(Sheet7[Line ID])))
Return count_*constant

     


    Paul Zheng
    Don't forget to hit THUMBS UP and mark it as a solution if it helps you!
     

littlemojopuppy's avatar
littlemojopuppy
Community Champion
6 years ago

Can you share the PBIX?

NaderSaeed's avatar
NaderSaeed
Helper II
6 years ago

Thanks for replying.

Unfortunately I can't, as its data is confidential.

But the example I gave roughly illustrates what I need to do.

 

For example,

For Line 1, Reason Z.

I want to multiply (0.23 * Count of Reason Z for Line 1), and do the same for each Line ID vs each Stop Reason.

i.e. Line 1, Reason A, Line 1, Reason X, and so on.

 

And then represent the data as follows, where the numbers on the graph are the values I get after the aforementioned multiplication for each line and reason.