Get the percentile of each "type" in a matrix with columns by "MonthYear"

[Moya_Novaes]

Let this dataset be a small reproducible example.

(Update: I've also created a column MonthYear which transforms e.g. 06/01/2018 to June-2018)

Date	Value	Type
06/01/2018	100	Gross Margin
06/01/2018	100	Gross Margin
06/01/2018	100	Gross Margin
06/01/2018	100	Gross Margin
06/01/2018	100	Gross Margin
06/01/2018	100	Gross Margin
06/01/2018	100	B
06/01/2018	100	B
06/01/2018	100	B
06/01/2018	100	C
06/01/2018	100	C
07/01/2018	100	Gross Margin
07/01/2018	100	Gross Margin
07/01/2018	100	Gross Margin
07/01/2018	100	B
07/01/2018	100	B
07/01/2018	100	B
07/01/2018	100	B
07/01/2018	100	B
07/01/2018	100	B
07/01/2018	100	C
07/01/2018	100	C

https://1drv.ms/x/s!AqdCRujnTMTHh0HpnhOVJuK3xwn5  .... (excel sharable link)

From this dataset I would like to create a Measure that gives me the Gross Margin Total, which will change by each month, just like the table bellow (in a matrix form).  Please note that I've left the calculation before the ~~ to augment the understanding

	June - 2018​	July - 2018​
Gross Margin​	600/600 ~~ 100%​	300/300 ~~ 100%​
B​	300/600 ~~ 50%​	600/300 ~~ 200%​
C​	200/600 ~~ 30%​	200/300 ~~ 66.6%​

I've tried to make this by the following calculation and some variations, which failed.

Measure = 
VAR CurrMonthYear = FIRSTNOBLANK(TABLE[MonthYear])


RETURN 
CALCULATE( SUM( TABLE[Value]), 
FILTER(ALL(TABLE), TABLE[Type] = "Gross Margin" && TABLE[MonthYear] = CurrMonthYear))

[MFelix]

Hi @Moya_Novaes ,

Use the following measure to achieve the desired result:

% Gross Margin =
SUM ( 'Table'[Value] )
    / CALCULATE (
        SUM ( 'Table'[Value] );
        FILTER ( ALL ( 'Table'[Type] ); 'Table'[Type] = "Gross Margin" )
    )

Regards,

MFelix

Regards,

MFelix

[Moya_Novaes]

I don't really know why, but my data is quite similar with the one I've created for this post and yet the denominator of the percentile gets the sum of all the database Gross Margin values, not the Gross Margin by the current MonthYear.

Any ideas why? I've been thiking about this even in my dreams 😛

@MFelix 

[MFelix · Accepted solution]

Hi @Moya_Novaes ,

You are picking up all the table for your filter so the Dates are also being ignored in the FILTER that is being used.

Try to Redo your measure to:

%DRE =
 (
    SUM ( DRE_CONTABILIDADE[Valor] )
        / CALCULATE (
            SUM ( DRE_CONTABILIDADE[Valor] ),
            FILTER (
                ALL ( DRE_CONTABILIDADE[Nível1] ),
                DRE_CONTABILIDADE[Nível1] = "(=) RECEITA OPERACIONAL LÍQUIDA"
            )
        )
) * 100

Regards,

MFelix

[Moya_Novaes]

@MFelixAlthough I've marked as answered it isn't. 

I've copied the code you posted and I got an infinity in the denominator on rows that are not "(=) RECEITA OPERACIONAL LÍQUIDA".

Personaly it dosn't make sense, any ideas?

[MFelix]

Hi @Moya_Novaes ,

Can you share a sample of the file?

Regards,

MFelix