Show Totals of categorised rows per month as percentage of a specific category of a row

[fdkza]

Hi 

I have a dataset( see attached) . 

Dataset

The Table structure is 

Stagename(Category)	Total	Date
A	Values	A date (yyyy/mm/dd)
B	Values	A date (yyyy/mm/dd)
C	Values	A date (yyyy/mm/dd)
D	Values	A date (yyyy/mm/dd)
E	Values	A date (yyyy/mm/dd)

Each stagename is unique to a day in a month and I want to display it in a report as 

A	B	C	D	E
100%	% of A	% of A	% of C	% of D

I have connected the day of month to a dim_date table so ideally when the user selects a time frame this table changes, 

I have experimented with the following formula but that calculates against all. How do i adjust the measure to calculate against a particular date only

%Against = divide(CALCULATE(sum([total]),'dim_date'[db_date]),CALCULATE(sum([total]),[stagename] = "Accounts Created"))

Any help will be greatly appreciated 🙂 

[v-frfei-msft]

Hi @fdkza,

I made one sample for your reference.

1. Create a calculated column.

rank = RANKX(ALL(Sheet1),Sheet1[stagename],,ASC,Dense)

2. Create a measure as below.

Measure = 
VAR maxrank =
    CALCULATE ( MAX ( Sheet1[rank] ) )
VAR SUMA =
    CALCULATE (
        SUM ( Sheet1[total] ),
        FILTER ( ALLSELECTED ( Sheet1 ), Sheet1[rank] = maxrank - 1 )
    )
RETURN
    IF ( MAX ( Sheet1[rank] ) = 1, 1, CALCULATE ( SUM ( Sheet1[total] ) ) / SUMA )

For more details, please check the pbix as attached.

Regards,

Frank

[fdkza]

Hi @v-frfei-msft

Thank you very much for this. I ideally want to display this using a date slicer at the top. How will i add the date to the measures ? 

Thanks for all your help so far! 

[v-frfei-msft]

Hi @fdkza,

I cannot get you, Could you please share your excepted result to me?

Regards,

Frank