Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi
I have some data in PowerBI that looks like this (this is much simplified):
The entries in yellow are duplicate population counts. There are two entries for this because both Brand 1 and Brand 2 cover the same town.
What I want to be able to do is have slicers that allow the user to select the brand, town etc and get the right results. I would then calculate % Population for whatever was selected.
I can get Power BI to calculate the population for each brand like this:
Brand Population = sumx(DISTINCT('Table'[Lookup]),FIRSTNONBLANK('Table'[Population],0))
...which looks for the first entry for Town1Date1 and ignores the rest. This gives Brand 1 = 30 and Brand 2 = 40. This works when brand or town filters are applied and gives the correct amount for each date.
But I cannot get it to work out the total monthly i.e. 10 + 12 = 22 (not 32) or grand total population i.e. 10 + 20 + 12 + 18 = 60 (not 70). I'd want something like:
Total Population =
CALCULATE(
sumx(DISTINCT('Table'[Lookup]),FIRSTNONBLANK('Table'[Population],0)),
ALLEXCEPT ('Table', 'Table'[Date]))
...so that it ignored the Brand and Town when adding up, giving a total for the month or overall, and I can calculate %s from that. But this does not work at all - it gives the same answer for each date. I also can't use ALLSELECTED because it doesn't work with sumx.
Thank you
Hi,
Share some actual date entries in the Date column and for that dataset, show the expected result.
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
114 | |
97 | |
95 | |
68 | |
65 |