Forum Discussion

Debbie_Montique's avatar
Debbie_Montique
Frequent Visitor
8 years ago
Solved

Countrows from Distinctcount from another column

Hi All,   I'm trying to do a count of all values from one column... got that, not a problem.  The problem is that I need my new count based on a distinct count from another column.  For example col...
  • Vvelarde's avatar
    Vvelarde
    8 years ago

    Debbie_Montique

     

    Good to know that you can solve your question.

     

    Please accept the solution to others con use you.

     

     

Vvelarde's avatar
Vvelarde
Community Champion
8 years ago

Debbie_Montique

 

Can you post a sample of the data to replicate it.

Regards

VIctor

Debbie_Montique's avatar
Debbie_Montique
Frequent Visitor
8 years ago

Hello,

 

Below is the data, please note that for each distinct SO Document that is B2B - price is $2.50, and for each distinct SO Document that is B2C - price is $1.65.

 

S.O.DocumentB2B/B2C
36094812B2B
36150140B2B
36211362B2B
36037098B2C
36094813B2C
36113575B2C
36132013B2C
36150156B2C
36189237B2C
36049426B2B
36066981B2B
36096529B2B
36159728B2B
36159733B2B
36170533B2B
36211343B2B
36244071B2C
36245048B2B
36034609B2B
36043892B2B
36045997B2B
36046243B2C
36046444B2B
36071046B2B
36170505B2B
35979970B2B
36008473B2B
36032993B2B
36035768B2B
36036393B2B
36044494B2B
36045814B2B
36046268B2B
36049434B2B
36049435B2B
36056082B2B
36056085B2B
36058503B2B
36058504B2B
36058513B2B
36058515B2B
36058516B2B
36058517B2B
36058518B2B
36058519

B2B

 

Here in my table I have the # of distinct orders by B2B/B2C as you suggested above, but I cannot multiply out the price per # of distinct orders:

 

 

Thank you very much!

 

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    8 years ago

    Debbie_Montique

     

    Try with this measure:

     

    Price =
IF (
    SELECTEDVALUE ( Table1[B2B/B2C] ) = "B2B";
    DISTINCTCOUNT ( Table1[S.O.Document] ) * 2,5;
    DISTINCTCOUNT ( Table1[S.O.Document] ) * 1,65
)

     

    You can made dinamically replacing the HardCoded Price with the AVG of the calculated column.

     

    Regards

     

    Victor 

    • Debbie_Montique's avatar
      Debbie_Montique
      Frequent Visitor
      8 years ago

      Victor, you are my HERO!!!  That works PERFECTLY.  I cannot thank you enough.  THANK YOU THANK YOU!!!

      • Vvelarde's avatar
        Vvelarde
        Community Champion
        8 years ago

        Debbie_Montique

         

        Good to know that you can solve your question.

         

        Please accept the solution to others con use you.