Forum Discussion

AnandRanga's avatar
AnandRanga
Helper III
9 years ago
Solved

Group by in DAX ?

I have written a formula in powerbi that is total hours/Billable hours. There can be more than 2 billable hours but there can't be more than one total hour. Total hour will be same for one date. But ...
  • ankitpatira's avatar
    ankitpatira
    9 years ago

    AnandRanga You can achieve Group By using DAX as below. Under Modelling tab click New Table and use below code to get output in the form of second table of your screenshot.

     

    Table = GROUPBY(TABLENAME,TABLENAME[date],TABLENAME[workinghours],"a",SUMX(CURRENTGROUP(), TABLENAME[hours]) )

AnandRanga's avatar
AnandRanga
Helper III
9 years ago

BhaveshPatel

This is the case

I want sum of hour field like for 16 sept - sum of hour will be 9 but for the working hours it should be 9 only. Now I am having this:

but the problem is I have written a utilization % formula where I have written Total hours / working hours. So it should be 47.50/36. But It's calculating 47.50/72 (8 times 9, by previous table).

So I want to create a measure where I want to group by working hours by date.

Thanks for your time. 

ankitpatira's avatar
ankitpatira
Community Champion
9 years ago

AnandRanga You can achieve Group By using DAX as below. Under Modelling tab click New Table and use below code to get output in the form of second table of your screenshot.

 

Table = GROUPBY(TABLENAME,TABLENAME[date],TABLENAME[workinghours],"a",SUMX(CURRENTGROUP(), TABLENAME[hours]) )

  • kohlivinayak's avatar
    kohlivinayak
    Resolver I
    8 years ago

    how to do this in direct mode as new table column option is not available ?

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi,

    Can we use two different tables in group by function? My columns are in different tables which I want to use in group by.

    If that is not possible, then what could be the alternate way to group by which allows taking two tables? 

     

    Thanks,

    Janki