Forum Discussion
Average Total Based on Total Distinct Count Only
Hi,
I am new to Power Bi, so please bear with me.
The column I am looking to create in Power BI is the last one here (Average Cost - Complete Total) where The [Total Cost] is always divided by the same number - the Distinct Total of the whole company (25):
| Site | Distinct Count of ID | Total Cost | Average Cost (Site) | Average Cost (Complete Total) |
| Site #1 | 25 | 5367 | 215 | 215 |
| Site #2 | 17 | 2342 | 138 | 94 |
| Site #3 | 5 | 1357 | 271 | 54 |
| Site #4 | 25 | 6368 | 255 | 255 |
| Site #5 | 12 | 2984 | 249 | 119 |
| Total | 25 |
I tried using MAX, but it keeps giving me the Average Cost (Site) column instead.
Any ideas?
5 Replies
So according to your description, the Average Cost (Complete Total) should be a measure, right? You can modify the measure using DAX formula below:
Average Cost (Complete Total) = MAX(Table1[Total Cost]) / CALCULATE(MAX(Table1[Distinct Count of ID]), ALL(Table1))
Regards,
Jimmy Tao
- This doesn't seem to work for me as my 'Distinct Count of ID' is a measure where: CountOfID = DISTINCTCOUNT(Table1[ID]) I get the error: Column 'CountofID' in 'Table1' cannot be found or may not be used this expression. Is there anyway around this? Thanks for your help!
Since MAX(measure) can't work, so you should change measure CountOfID to calculate column and use the formula.
Regards,
Jimmy Tao
Have you solved your issue by now? If you have, could you please help mark the correct answer to finish the thread? Your contribution will be much appreciated.
Regards,
Jimmy Tao
Hi,
Assuming columns 2 and 3 are measures, try this measure
=[Total Cost]/CALCULATE([CountofID],ALL(Data[Site]))
Hope this helps.
