Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.