Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Need help badly - Percentile and Visualization

Hello Guys,

I need some help and I got stuck on this for awhile already.

 

So I have this Table

DateProductX
1/2/1990A0.017791
1/3/1990A-0.00256
1/4/1990A-0.00818
1/5/1990A-0.00974
1/8/1990A0.0046
1/9/1990A-0.01179
1/10/1990A-0.00658
1/11/1990A0.003511
1/12/1990A-0.02452
1/13/1990A0.022791
1/14/1990A0.002436
1/2/1990B-0.00318
1/3/1990B-0.00474
1/4/1990B0.0096
1/5/1990B-0.00679
1/8/1990B-0.00158
1/9/1990B0.003635
1/10/1990B0.008846
1/11/1990B0.014057
1/12/1990B0.019269
1/13/1990B0.02448
1/14/1990B0.029691
1/2/1990C0.008511
1/3/1990C-0.01952
1/4/1990C0.029791
1/5/1990C0.009436
1/8/1990C0.003822
1/9/1990C0.002262
1/10/1990C0.0166
1/11/1990C0.000212
1/12/1990C0.005423
1/13/1990C0.015511
1/14/1990C-0.01252

 

I want to calculate percentile but I need to show by % the difference between TWO product at a time.
Showing percentile for the two products chosen is the one i got stuck plus how to calculate without giving errors

Lets say i pick Product A and C and chooses date range from 1/2 to 1/12
This should be the finish result.

Formula :
1st Product = PERCENTILE.EXC(1st Product X, PERCENTAGE)
2nd Product = PERCENTILE.EXC(2nd Product X, PERCENTAGE)
Result = 1st Product - 2nd product

*see the one i circle below





PBIX File : PBIX File Link 

Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    Here is one way to do it.  Make two tables with these expressions with no relationship to the others.

    PctValues1 = GENERATESERIES(0.1,0.9,0.1)
    PctValues2 = GENERATESERIES(0.1,0.9,0.1)
     
    Put one column on rows and the other one on columns in a matrix, along with this measure.
     
    NewMeasure = var firstproduct = MIN(ProductsX[Product])
    var lastproduct = MAX(ProductsX[Product])
    var pct1 = Min(PctValues1[Value])
    var pct2 = Min(PctValues2[Value])
    var prod1pct = CALCULATE(PERCENTILEx.EXC(ProductsX, ProductsX[X], pct1), ProductsX[Product]=firstproduct)
    var prod2pct = CALCULATE(PERCENTILEx.EXC(ProductsX, ProductsX[X], pct2), ProductsX[Product]=lastproduct)
    var result = prod1pct-prod2pct
    return result
     
    Regards,
    Pat

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    Here is one way to do it.  Make two tables with these expressions with no relationship to the others.

    PctValues1 = GENERATESERIES(0.1,0.9,0.1)
    PctValues2 = GENERATESERIES(0.1,0.9,0.1)
     
    Put one column on rows and the other one on columns in a matrix, along with this measure.
     
    NewMeasure = var firstproduct = MIN(ProductsX[Product])
    var lastproduct = MAX(ProductsX[Product])
    var pct1 = Min(PctValues1[Value])
    var pct2 = Min(PctValues2[Value])
    var prod1pct = CALCULATE(PERCENTILEx.EXC(ProductsX, ProductsX[X], pct1), ProductsX[Product]=firstproduct)
    var prod2pct = CALCULATE(PERCENTILEx.EXC(ProductsX, ProductsX[X], pct2), ProductsX[Product]=lastproduct)
    var result = prod1pct-prod2pct
    return result
     
    Regards,
    Pat
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Wow i didnt know i dont need relationship on this.
      This is really helpful and the same time learn something from this!

       

      thank you 
      parry2k mahoneypat