Expression/ measures based on the give data Ask

[Bakhtawar]

i attached some data .. based on that data i need expression of these questions

1. Percentage and number of the host with the multi-listing from the total host.
2. Percentage and number of the single-listing from the total host.

3. Show top 10 of the host that have the most listing

4. Show top 10 of the host that have the most expensive listing (AverageTotal Price per day including all expense and deposit with assumption that no extra guest is requested)

5. Percentage of listing based on the property type

6. Average price/night (Only the daily price without other expense) for each of the property type

7. Average price/night (Total price with assumption no additional guest) for all property type.

8. Display the average price for each type of the listing on different locations. You need to display this data in order to capture each of the information.

I tried this expression for 1 and 2 .. but i am not sure this is correct or not

count(Aggr(NODISTINCT sum(HostID),HostID))

I need expression / measures based on data .. kindly any one help please

data file

https://www.dropbox.com/scl/fi/dl63c2slymmue4p55xmuy/data.xlsx?dl=0&rlkey=oy4ryt0h5vaxumrlbjeshk212

[Icey]

Hi @Bakhtawar ,

Please check the attached PBIX file.

I don't quite understand some of your requirements. Therefore, the measures in the attached PBIX file are for reference only.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Bakhtawar]

@Icey  which question yuo did not understand ?

[Icey]

Hi @Bakhtawar ,

1. When it comes to average price, which columns are used to calculate?

2. Are the cleaning fee, extra fee, etc. collected on a daily or one-time basis?

Best Regards,

Icey

[Bakhtawar]

@Icey  price column

[Icey]

Hi @Bakhtawar ,

Please refer to the attached PBIX above and change the expressions to what you want. If you have any questions during this period, please let me know.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

Well, I can tell you that your code is not correct. 🙂  Count is a valid DAX function but not Aggr or NODISTINCT, no idea where those came from. Having some difficulty understanding your requiremens. 

You could do number 7 like AVERAGEX(ALL('Table'),[Price]).

I think you should refer to this: https://docs.microsoft.com/en-us/dax/dax-function-reference

[Bakhtawar]

@Greg_Deckler  have you check the document data ? there is several fields in that document .. kindly check . mostly i think question depend on Host ID and price.. and also property type column / field is also in data..

kindly check data once .. you better understand it

and i just want dax functions according to questions.. i already give the data

[Greg_Deckler]

Yes @Bakhtawar I have the Excel file open and I am looking at it, or I was just prior to responding to this post. The problem is I am not understanding things like:

1. Percentage and number of the host with the multi-listing from the total host.

OK, number of the host, so are you saying the number of distinct hosts or the rows for each host? multi=listing from the total host. I have no idea what that means. Are you saying that you want the number of rows of each host divided by the total number of rows for all hosts?

[Bakhtawar]

multi listing means if host id has duplicate records

if Host ID has duplicate records then total count of that host ie.. if 5 ID has 10 records then

host id   count

5            10

6            20

@Greg_Deckler'

and single listing means if Host ID did not repeat