Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
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.
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)
What could I be doing wrong?
Thanks!!
I picked SUM just as a guess. Use whatever aggregation is appropriate for your case.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |