cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Pierrechapar
Frequent Visitor

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.

 

Pierrechapar_0-1634566456913.png

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

Pierrechapar_1-1634567093362.png

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,

 

4 REPLIES 4
Pierrechapar
Frequent Visitor

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.

Pierrechapar_0-1634656670214.png

 

 

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.

Pierrechapar_1-1634656939560.png

 

 

 

 

 

AlexisOlson
Super User
Super User

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 )

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)

Pierrechapar_0-1634632834017.png

 

What could I be doing wrong?

 

Thanks!!

 

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

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!