Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
Here is my data, with the column I'd like to create.
I want a column that divides B by C, but when the ID is the same, returns the average of all those calculations for that particular ID. For example, with ID=3 there is only 1 value, so the % is returned, but for ID=7 there are two values so (0.214+0.478)/2 is returned for all rows with ID=7.
I hope that makes sense. I know I could just set the default summarization of the % row to average, rather than sum, but I will be unpivoting the table in another step.
Thanks for your help
Sean
Solved! Go to Solution.
I created % measure using the below formula:
Average (B,C) = SUM(Table1[B])/SUM(Table1[C])
For the new measure, try using the below formula:
New Measure = SUMX(ALLEXCEPT(Table1,Table1[ID]),[Average (B,C)])/CALCULATE(COUNTROWS(Table1),ALLEXCEPT(Table1,Table1[ID]))
I created % measure using the below formula:
Average (B,C) = SUM(Table1[B])/SUM(Table1[C])
For the new measure, try using the below formula:
New Measure = SUMX(ALLEXCEPT(Table1,Table1[ID]),[Average (B,C)])/CALCULATE(COUNTROWS(Table1),ALLEXCEPT(Table1,Table1[ID]))
@Anonymous
It seems that ID to currency is one to many.
What you can do is the following
1. Create a table with ID as first column
2. B Column will be a measure
Measure_B = SUM(Table[Column_B])
3. C Column will be a measure
Measure_C = SUM(Table[Column_C])
4. Desire Column will be:
( [Measure_B] / [Measure_C] ) / COUNT(Table[Column_ID]
5. Currency wont be included in this table
Not sure what you mean. There's no currency involved. This is my fact table. ID and A both connect to dimensions.
When you say B column will be a measure, do you mean creating a column or creating a measure? Aren't those different things?
I couldn't get your steps to work.
@Anonymous
Sorry i thought A was a currency field.
Yes i meant create a measure based on Column B and also based on Column C.
Then another measure as described on step 4.
Finally on the table put ID, and then the 3 measures above
User | Count |
---|---|
98 | |
89 | |
82 | |
71 | |
67 |
User | Count |
---|---|
114 | |
104 | |
101 | |
72 | |
65 |