Counting distinct values based on criteria

[GrimReaperX]

Hello guys,

I have a data where PO number, cost center and division is given.

Based on specific cost center a PO is considered either division A or B. It is possible a PO number might have more than 1 line and can be assigned both divisions, A and B. When a PO has 2 line items and assigned to division A in any line, i want to count this PO as division A PO, disregarding division B in the second line in the any line 1 or 2.

Hence, my question is how to distinctcount PO number and evaluate a PO as division A PO even it has second or third line that are assigned to division B or any  division other than division A.

Example is below;

PO number/Division/Cost Center

8000000001/IT/001

8000000001/FIN/002

8000000002/FIN/004

8000000003/IT/003

8000000003/IT/001

Based on example above, when you count distinctvalues for IT, you should get 2 and 1 for FIN as you ignore second line for the PO number 8000000001 and consider it as IT PO.

Thank you very much.

[v-henryk-mstf]

Hi @GrimReaperX ,

Whether the advice given by @AlB @Anonymous  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

[Anonymous]

Hello*

Try these 2 measures

calculation IT =

var ponbr=selectedvalue(Feuil2[PO Number])

var _step1=

filter(

ADDCOLUMNS (

SUMMARIZE ( Feuil2, Feuil2[PO Number] ),

"@on IT",

CALCULATE (

COUNT ( Feuil2[Division] ),

ALLEXCEPT ( Feuil2, Feuil2[PO Number] ),

Feuil2[Division] = "IT"

)

),[@on IT]>0)

var result=

countrows(_step1)

return

result

calculation FIN =

var ponbr=selectedvalue(Feuil2[PO Number])

var _step1=

filter(

ADDCOLUMNS (

SUMMARIZE ( Feuil2, Feuil2[PO Number] ),

"@on IT",

CALCULATE (

COUNT ( Feuil2[Division] ),

ALLEXCEPT ( Feuil2, Feuil2[PO Number] ),

Feuil2[Division] = "IT"

)

),[@on IT]=0)

var result=

countrows(_step1)

return

result

[AlB]

Hi @GrimReaperX 

How do you determine from the data whether 8000000001  has to be assigned to IT or FIN?

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[GrimReaperX]

Hi @AlB,

It is determined based on cost center. Cost center 001 is for IT while 002 for Finance. 

Thanks for quick turn around.

[AlB · Accepted solution]

@GrimReaperX 

See attached file for a possible solution

Measure = 
VAR currentDept_ = SELECTEDVALUE(Table1[Dept])
RETURN
CALCULATE(
VAR auxT_ =  ADDCOLUMNS(ALL(Table1[PO]), "@CC", CALCULATE(MIN(Table1[CostCenter])))
VAR auxT2_ = ADDCOLUMNS(auxT_, "@Dept", CALCULATE(MAX(Table1[Dept])))
RETURN
COUNTROWS(FILTER(auxT2_, [@Dept] = currentDept_))
, ALL(Table1[Dept]))

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[GrimReaperX]

I am trying to get the logic of this function but cannot relate SELECTEDVALUE within this function. Why it is used and necessary for this measure? 

[AlB]

@GrimReaperX 

Note we have an ALL(Table1[Dept]) as last parameter on the main calculate . We need that to make the calculation across all departments. However, in the countrows we want to limit the count to the department currently on the filter context (for instance in the rows of your table visual). After the ALL(Table1[Dept]) that info will be lost, so as first step we get the current department through the SELECTEDVALUE() and store it in a variable. Then we use it to filter for that department in the COUNTROWS()  

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[GrimReaperX]

Thanks for bearing my comments. I've tried this formula with different databases too and it works perfectly. My understanding of this formula is below;

VAR currentDept_ = SELECTEDVALUE(Table1[Dept]) --> If Dept column has one value return the following result

RETURN

CALCULATE(

VAR auxT_ = ADDCOLUMNS(ALL(Table1[PO]), "@CC", CALCULATE(MIN(Table1[CostCenter]))) --> This expression creates a virtual table including all values from Table1 and creates additional column of Cost center. How MIN function affects newly createdCostCenter column, why MIN is used?  

VAR auxT2_ = ADDCOLUMNS(auxT_, "@Dept", CALCULATE(MAX(Table1[Dept]))) --> 
Reflects previous table with additional line where MAX Dept from Table1 is added. My question is how to calculate MAX value of Dept as it is text value? What is the function of this formula here?

RETURN

COUNTROWS(FILTER(auxT2_, [@Dept] = currentDept_)) , ALL(Table1[Dept])) --> 

Returning final result as counting all rows in the virtual table auxT2 where DEPT = currentDept 

I would really appreciate if you can bear this question too as it is complex formula which i want to literally get.

Thank you very much