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.
Good Day, I have been struggling with transforming an Excel workbook into Power BI Desktop. The custom column is "Pop. per Density", and its an animal "Population" divided by a denominator. That denominator is the Median of the "Pop. Density" if the animal in the row matches the the animals in the "Animal" column.
Excel Formula:
Pop. per Density = IFERROR([@Population]/MEDIAN(IF([Animal]=[@Animal],[Pop. Density],"")),"")
I am still a novice with DAX, but in Excel the formula is straight forward. I just have no idea on how to reference a cell in a column, in PBI, and I cant seem to find a troubleshoot on that either.
Pop. per Density = IF( ISERROR( SEARCH("Cat", 'Animals'[Animal]) ), 0, CONVERT(MEDIAN( 'Animals'[Pop. Density]), INTEGER))
I just want to replace that string "Cat" with the value of the cell. Am I close?
Animal | Population | Pop. Density | Pop. per Density |
Cat | 2.97 | ||
Fox | 4.51 | 29.02 | 0.15 |
Eagle | 2.11 | ||
Snake | 1.34 | ||
Dog | 5.62 | 18.64 | 0.39 |
Owl | 0.11 | ||
Dog | 1.28 | 4.54 | 0.09 |
Monkey | 1.17 | ||
Dog | 7.62 | 14.30 | 0.53 |
Frog | 12.45 |
Any help on the above would be most appreciated!
Solved! Go to Solution.
@RiskyBiscuts
Can you see if the following calculated column solves your problem?
Pop. per Density =
var __ani = Animal[Animal ]
var __deno =
CALCULATE(
MEDIAN( Animal[Pop. Density] ),
Animal[Animal ] = __ani,
REMOVEFILTERS(Animal)
)
var __pop = Animal[Population ]
return
DIVIDE( __pop , __deno )
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@RiskyBiscuts
Can you see if the following calculated column solves your problem?
Pop. per Density =
var __ani = Animal[Animal ]
var __deno =
CALCULATE(
MEDIAN( Animal[Pop. Density] ),
Animal[Animal ] = __ani,
REMOVEFILTERS(Animal)
)
var __pop = Animal[Population ]
return
DIVIDE( __pop , __deno )
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Spot on! Yes this is what I meant to solve. Only thing I need to do is ignore the 0 and null values in the "Pop. per Density", as it turns I get a null value when 50% of the "Pop. Density" values are less then 0 or null. Do you know how I can ignore those 0 and null values?
@RiskyBiscuts
If you want to get rid of the blank and zero lines then you will have to create another with filter as follows:
New Animal = FILTER( Animal , Animal[Pop. per Density] <> BLANK() )
Or, you can create a measure as blanks are automatically hidden
Measure Pop. per Density =
var __ani = SELECTEDVALUE(Animal[Animal ])
var __deno =
CALCULATE(
MEDIAN( Animal[Pop. Density] ),
Animal[Animal ] = __ani,
REMOVEFILTERS(Animal)
)
var __pop = SELECTEDVALUE(Animal[Population ])
return
DIVIDE( __pop , __deno )
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@RiskyBiscuts - I think what you want is:
Pop. per Density Column =
MEDIANX(FILTER('Animals',[Animal]=EARLIER('Animals'[Animal])),[Pop. Density])
Also, you can find a lot of Excel to DAX translations here: Excel to DAX Translation - Microsoft Power BI Community
Hey! Thanks for that link, its going to definately help me out!
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |