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,
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
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 |
---|---|
107 | |
94 | |
77 | |
63 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |