Forum Discussion

Karthick1006's avatar
Karthick1006
Frequent Visitor
1 year ago
Solved

Row level Subtotal Error

I have the following table    I am using the following DAX query to find the % of customer  new_=DIVIDE(     CALCULATE(sum(Table1[records_valid]), LASTNONBLANK(Table1[extraction_date],SUM(...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Karthick1006 ,

     

    You can achieve this by first creating a calculated column to get the latest record for each country. Then, build a measure that calculates the valid percentage using only those latest records. This way, your row-level and region-level totals will reflect only the most recent data per country. Here's a rough idea of how the logic works:

     

    Please create a calculated column as belolw:

     

    IsLatestRecord =
    VAR CurrentDate = 'Table'[Date]
    VAR Country = 'Table'[Country]
    VAR MaxDate =
    CALCULATE (
    MAX ( 'Table'[Date] ),
    ALLEXCEPT ( 'Table', 'Table'[Country] )
    )
    RETURN IF ( CurrentDate = MaxDate, 1, 0 )


           Then create a measure as below:

    ValidCustomer% (Latest Only) =
    VAR LatestData =
    FILTER (
    'Table',
    'Table'[IsLatestRecord] = 1
    )
    VAR TotalValid = SUMX ( LatestData, 'Table'[records_valid] )
    VAR TotalCount = SUMX ( LatestData, 'Table'[records_count] )
    RETURN DIVIDE ( TotalValid, TotalCount ) * 100

     

     

    If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

    Regards,
    B Manikanteswara Reddy

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi Karthick1006 ,

 

Thank you for reaching out to Microsoft Fabric Community.

bhanu_gautam Thank you for your quick response.

 

Please try using the DAX formula below:

 

new_Measure =
DIVIDE(
CALCULATE(
SUMX(Table1,Table1[records Valid])
),
CALCULATE(
SUMX(Table1,Table1[records count])
),
0
)

 

If this doesn't work as expected, could you please share the expected result or a sample output you're aiming for? That would help a lot in providing a more accurate solution.

 

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

 

Regards,

B Manikanteswara Reddy

  • Karthick1006's avatar
    Karthick1006
    Frequent Visitor
    1 year ago

    Hi Anonymous ,

     

    Thanks for replying.

     

    The above measure didn't resolve the issue.

    I requirement is that for each country the latest record should be considered to find the average customer value. For Region level it should consider all the countries under the region.

    It should consider only the highlighted rows for row level total and row level subtotal, as they are 

    recent records.

     

    The calculation has to be as follow

    Row Level Calculation:

    Ethiopia: (226/3534)*100 = 6.39%

    Namibia: (0/13873)*100 = 0.0%

     

    Row Level subtotal should consider Ethiopia and Namibia

    AME: ((226+0)/(3534+13873))*100 = 1.29%

    This is my desired output.

     

    Kindly write back if you need any further information.

     

    Regards,

    Karthick M

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi Karthick1006 ,

       

      You can achieve this by first creating a calculated column to get the latest record for each country. Then, build a measure that calculates the valid percentage using only those latest records. This way, your row-level and region-level totals will reflect only the most recent data per country. Here's a rough idea of how the logic works:

       

      Please create a calculated column as belolw:

       

      IsLatestRecord =
      VAR CurrentDate = 'Table'[Date]
      VAR Country = 'Table'[Country]
      VAR MaxDate =
      CALCULATE (
      MAX ( 'Table'[Date] ),
      ALLEXCEPT ( 'Table', 'Table'[Country] )
      )
      RETURN IF ( CurrentDate = MaxDate, 1, 0 )


             Then create a measure as below:

      ValidCustomer% (Latest Only) =
      VAR LatestData =
      FILTER (
      'Table',
      'Table'[IsLatestRecord] = 1
      )
      VAR TotalValid = SUMX ( LatestData, 'Table'[records_valid] )
      VAR TotalCount = SUMX ( LatestData, 'Table'[records_count] )
      RETURN DIVIDE ( TotalValid, TotalCount ) * 100

       

       

      If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

      Regards,
      B Manikanteswara Reddy

       

      • Karthick1006's avatar
        Karthick1006
        Frequent Visitor
        1 year ago

        Thank you very much Anonymous, the issue is resolved now. 
        I appreciate the effort you took to help me.

         

        Cheers,

        Karthick M