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(Table1[records_valid])))
    , CALCULATE(sum(Table1[records_count]), LASTNONBLANK(Table1[extraction_date],SUM(Table1[records_count])))
    , 0
)

In the Matrix visual, the row level subtotal (AME) value is considering only row (country) with max date

 

I want the row level subtotal to consider all the countries under AME region (Ethiopia & Namibia). Can anyone help me with this?

Regards,

Karthick M




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

     

6 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    Karthick1006 , Try using

    dax
    new_ =
    DIVIDE(
    CALCULATE(
    SUM(Table1[records_valid]),
    ALLEXCEPT(Table1, Table1[Region])
    ),
    CALCULATE(
    SUM(Table1[records_count]),
    ALLEXCEPT(Table1, Table1[Region])
    ),
    0
    )

    • Karthick1006's avatar
      Karthick1006
      Frequent Visitor
      1 year ago

      Hi bhanu_gautam,

      This doesn't solve the problem


      Thanks for trying, I have added few of more rows of data for your reference

  • 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