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

     

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