Forum Discussion

GrimReaperX's avatar
GrimReaperX
Icon for Helper II rankHelper II
4 years ago
Solved

Counting distinct values based on criteria

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.

General Comment
Need Help
Show and Tell
Tips and Tricks
  • AlB's avatar
    AlB
    4 years ago

    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.

     

8 Replies

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Icon for Community Support rankCommunity Support
    4 years ago

    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's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      GrimReaperX
      Icon for Helper II rankHelper II
      4 years ago

      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's avatar
        AlB
        Icon for Community Champion rankCommunity Champion
        4 years ago

        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.