Forum Discussion

Pierrechapar's avatar
Pierrechapar
Frequent Visitor
4 years ago

Help with % in stacked column chart.

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,

 

Need Help

4 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    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's avatar
      Pierrechapar
      Frequent Visitor
      4 years ago

      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's avatar
        AlexisOlson
        Super User
        4 years ago

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

  • Pierrechapar's avatar
    Pierrechapar
    Frequent Visitor
    4 years ago

    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.