Row level Subtotal Error

[Karthick1006]

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

[v-bmanikante]

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]

Hi @v-bmanikante ,

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

[v-bmanikante · Accepted solution]

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]

Thank you very much @v-bmanikante, the issue is resolved now. 
I appreciate the effort you took to help me.

Cheers,

Karthick M

[bhanu_gautam]

@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]

Hi @bhanu_gautam,

This doesn't solve the problem

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