Forum Discussion

NETO13's avatar
NETO13
Regular Visitor
6 years ago

Count a dynamic measure

Hi,   I'm trying to calculate a dinstinct count of rows for a specific measure and couldn't find a way to do this. The scenario: I have a table with customers, quarters, quarter aging, revenue an...
Need Help
Ashish_Mathur's avatar
Ashish_Mathur
Super User
6 years ago

Hi,

Share the link from where i can download your PBI file with all your calculations.

  • NETO13's avatar
    NETO13
    Regular Visitor
    6 years ago

    Hi Ashish_Mathur ,

     

    sorry but I can't share the PBI that I'm working for.  See if the below helps to understand better:

     

    this is what the data looks like:

    Customer IDCustomer NameRegionProductQuarterQuarter AgingQuarter Week NumerOrders RevenueOrders Margin
    2134590ABBUSZZ2020-Q2-41$1,000,000.00$500,000.00
    1235857ACCBRZZ2020-Q3-32$950,000.00$380,000.00
    9023411ADDMXXX2020-Q1-53$850,000.00$297,500.00
    8923452AEEARXX2020-Q4-23$789,200.00$276,220.00
    4390563AFFCLZZ2019-Q2-82$589,000.00$235,600.00
    2348595EGGCOLCC2020-Q3-37$120,350.00$60,175.00
    1010293FFGARVV2020-Q3-38$1,000,500.00$300,150.00
    1928350BBBBRVV2019-Q2-810$700,589.00$280,235.60
    2348950EDDPRCC2020-Q1-111$123,989.00$61,994.50
    2134590ABBUSZZ2021-Q2010$4,398,123.00$1,099,530.75
    1235857ACCBRZZ2021-Q1-113$3,895,040.00$1,129,561.60
    9023411ADDMXXX2021-Q205$89,034.00$44,517.00
    8923452AEEARXX2021-Q1-16$120,930.00$60,465.00
    4390563AFFCLZZ2021-Q1-15$590,320.00$177,096.00

     

    Measures created:

    • Total revenue: removing any quarter selected - 
      CALCULATE(SUM(Actual[Orders Revenue]),ALL('Quarter'[Quarter]))
       
    • LY Revenue: conditional based on the quarter selected - nested IF:

    LY Rev = IF(FIRSTNONBLANK('Quarter'[Quarter Aging],1)="0",

    CALCULATE([Total Rev],'Quarter'[Quarter Aging]="-4"),
    IF(FIRSTNONBLANK('Quarter'[Quarter Aging],1)="-1",
    CALCULATE([Total Rev],'Quarter'[Quarter Aging]="-5"),
    IF(FIRSTNONBLANK('Quarter'[Quarter Aging],1)="-2",
    CALCULATE([Total Rev],'Quarter'[Quarter Aging]="-6").... and goes for -13 Quarter Aging.
     
    • Running total Revenue QTD and LY - this is to sum the revenue as the weeks of the quarter goes by to give cummulative revenue
    RTotal Rev =
    CALCULATE (
    SUM ( Actual[Orders Revenue] ),
    FILTER ( ALLSELECTED('Quarter'),
     
    'Quarter'[Quarter Week Num] <= MAX ( 'Quarter'[Quarter Week Num] )
    ))
     
    • Y/Y Rev and margin comparison
    Y/Y Rev =
    (ROUNDUP([RTotal Rev],3) / [RTotal LY Rev]) -1
     
    • And measures for the Account classification
    Quadrant I = IF(AND([Y/Y Rev]>=0,[Y/Y GM]>=0),"I",BLANK())
    Quadrant II = IF(AND([Y/Y Rev]<0,[Y/Y GM]>=0),"II",BLANK())
    Quadrant III = IF(AND([Y/Y Rev]<0,[Y/Y GM]<=0),"III",BLANK())
    Quadrant IV = IF(AND([Y/Y Rev]>0,[Y/Y GM]<=0),"IV",BLANK())
     
     
    Situation
    If I select a TABLE View and put Account name and quadrant, I can have the list of the accounts that are being classified within that, but I don't have a way to count how many accounts are those.
     
    For each time that I select a quarter, all the measures calculate the LY and Y/Y and give a different classification, as you can see in the formulas.
     
    Outcome expected: be able to quantify how many accounts are increasing and/or decreasing Y/Y depending on the quarter selected.