Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
In a tabular model I have a table with collumns like date, name, type and NumberOfDays (integer). The date-collumn is hided because there is another table period, with the data, month and year.
There are 6 different namens and 2 different types. Every combination of name/type has at most one value (NumberOfDays) for every monday.
Example:
Date Name Type NumberOfDays
20190107 abc 1 20
20190107 abc 2 12
20190114 abc 1 2
20190114 def 1 4
20190121 abc 1 8
20190121 def 1 11
20190121 ghi 2 9
In my pbix I have slicers for year, month and type.
What I want is to display one value, which is computed as follows:
- calculate for every name, with respect to the given filtervalues, the average NumberOfDays.
- display the lowest average of the 6 names. Names that have average = blank, don't need to be taken into account.
In SQL I could do this with something like average over partition by and than take the minimum. But in Power BI nothing works. Because my source is a tabular model, I can't add columns or tables in my pbix.
Who can help me?
for your first bullet, if you put Name on rows, and all your other filters as slicers and what not, i think you could just use
Avg Days = AVERAGE( Table2[NumberOfDays] )
I dont quite follow your second bullet point though.
Hi Nick,
Thanks for your reply!
My second bullet is the one I can't get working. In your example you have the averages per name. What I want is to display the minimum of the list with averages, as a single value. In the example, the minimum of the firts list with averages, is 7.50 and in the second list it's also 7.50.
Is this more clear?
User | Count |
---|---|
93 | |
83 | |
77 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |