Divide Measure and calculated column issue

[MarcW]

Hello, everybody,

I have a problem that's been stuck in my head for three weeks.

The situation:
In my raw data table there is a value column with 20,000 rows. About 10,000 rows have a value <>0.
I calculate the number with Count value = COUNTAX(FILTER(Merge;Merge[Value€ ]<>0);Merge[Value € ]). The sum of the column is currently 1.000.000 €.

The target value is 2,000,000 € and is an individually defined total value that has nothing to do with the data set. Now I want to distribute this to each row with a value <> 0. The idea:

First I calculate a factor per measure: Target factor = DIVIDE(2.000.000/10.000;SUM(Merge[value €]);0)

Then a new calculated column is inserted in which I multiply the following:

Target total = Merge[Value €] * Target factor
= in total 2.000.000 €

So far the whole thing works and I get a correct result.

To keep the whole dynamic I have adjusted the Measure target factor as follows:

Target factor = DIVIDE(2.000.000/Count value;SUM(Merge[value €]);0)

At this point the whole thing doesn't work anymore. The sum of Target total is always much higher. In this example the result is 20.000.000.000. As you can see it is multiplied by the value I calculate at count value. Afterwards I tried to divide the column Target total again by Count value, unfortunately I get an error message here.

I just can't figure out why it works if I enter the result of Count value = 10.000 as number instead of the Measure Count value.

Thanks in advance for your help. I am also very open for alternative solutions.

[WillyW]

Hi,

I think you need DISTINCTCOUNT instead of count.

Target factor = DIVIDE(2.000.000/DISTINCTCOUNT(value);SUM(Merge[value €]);0)

[MarcW]

Hi,

thanks for your reply. Unfortunalety this doesn`t work, because there are sometimes the same values in each row. With DISTINCTCOUNT I get much less counts than I actually have per line.

Thanks!

[WillyW]

I'd suggest to add a column with unique numbers from 1 to x (or maybe you have order numbers or kind of IDs you can use?), then you should be able to use DISTINCTCOUNT on this column.