Help with % in stacked column chart.

[Pierrechapar]

Hi all,

I've been looking aroud to see if I could find anything to help me solve my query. Since I didn't I turn ove tou you guys for  help.

I have this stacked column chart that displays number of distincts customers that buy per month. As you can see in the pic, it's based on month/year of purchase with a series or month/year of registry.

I'm trying to convert it to show the % of each series based on the 1st month of that series.

So the expected outcome would be:

2020 Jan 100% (series Jan2020)

2020 Feb 32.6% (series Jan2020) / 2020 Fed 100% (series Feb2020)

2020 Mar 11.8% (series Jan2020) / 2020 Mar 28.5 (series Feb 2020) / 2020 Mar 100% (series Mar2020)

And so on. Basically something like this

I understan the logic, that the distinctcount should be divided bay de first period of the series. But I can't wrap my head around referencing that particular period.

Any help will be very much appreciated.

Regards,

[Pierrechapar]

I've found a very simple way around this issue.

I created a 2 columns in the table to reference the series and a Disctinctcount of customers, based on the parameters that are on the slicers. As I get the same number in mutiple row I just used a MIN tu get 1 number in particular.

The Problem now is that althoughis working fine for individual countries in a slicer, as soon as I choose 2 or more it does de distinctcount correctly, but it doesn't sum up the reference period.

I modified the formula not to use the min, but to sum up the references for those countries using a Rank so I don't repeate any unwated data. This doesn't work. It still only takes the numbers of 1 country instead of the sum of all the selected.

[AlexisOlson]

You can define a measure that calculates the first month's value and then scale all the other values by dividing by that amount.

Something like this:

BaseValue =
VAR FirstMonth = CALCULATE ( MIN ( Table1[Date] ), ALLSELECTED () )
RETURN
    CALCULATE ( SUM ( Table1[Amount] ), ALL ( Table1 ), Table[Date] = FirstMonth )

[Pierrechapar]

Thanks Alexis for the response.

Just a quick question. Why a SUM in the return calculate? The actual measure is a DistinctCount(id_customer).

I ask because I don't have any field to SUM.

Any way, I tried with the formula you mention with SUM and with DistinctCount and it doesn't work.

The end measure used is = Divide(Distinctcount(id_customer), BaseValue)

What could I be doing wrong?

Thanks!!

[AlexisOlson]

I picked SUM just as a guess. Use whatever aggregation is appropriate for your case.