Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |