Forum Discussion

Raj12's avatar
Raj12
Icon for Helper III rankHelper III
3 years ago
Solved

Count records in table ignoring groups in a table in Power Bi

Hi,

I want to get pecentage per group i.e Total value of a Group/ Total Value
I am trying to get total value of all groups from the data table but when I put that measure in a table it gets divided by groups which I don't want.
If I used "ALL" in measure then it will even ignore Date but I need my measure to be filtered by date not by group.

Data Table:

IDGroupDate
12TUI01/02/2023
13FOR02/02/2023
14GPP03/02/2023
15FOR04/02/2023
16FOR05/02/2023
17TUI06/02/2023
18FOR07/02/2023
19GPP08/02/2023
20GPP09/02/2023

Output Table:

Table ColumnsMeasure
GroupCountTotal CountPercentage
TUI280.25
FOR480.5
GPP380.375


Measure that I Created:

Client Tagged =
var mon_start_date =     CALCULATE(MIN(Table[Date]  ),ALLSELECTED( Table[Date]   ))
var mon_end_date =     CALCULATE(Max(Table[Date]  ),ALLSELECTED( Table[Date]   ))
return
CALCULATE(DISTINCTCOUNT(Table[ID]),ALL(Table),ALRWDCLI[Date]>=mon_start_date && ALRWDCLI[Date]<=mon_end_date)
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Raj12 ,

    Why is the total 8 and not 9?

    Please check if this is your expected output.

    pecentage per group = 
VAR _count = COUNTROWS('Table')
VAR _count_all = CALCULATE(COUNTROWS('Table'),ALL())
VAR _result = DIVIDE(_count,_count_all)
RETURN
_result

     If you need the total to always be at 100%, try this.

    VAR _count = COUNTROWS('Table')
VAR _count_all = CALCULATE(COUNTROWS('Table'),ALLSELECTED())
VAR _result = DIVIDE(_count,_count_all)
RETURN
_result

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Raj12 ,

    Why is the total 8 and not 9?

    Please check if this is your expected output.

    pecentage per group = 
VAR _count = COUNTROWS('Table')
VAR _count_all = CALCULATE(COUNTROWS('Table'),ALL())
VAR _result = DIVIDE(_count,_count_all)
RETURN
_result

     If you need the total to always be at 100%, try this.

    VAR _count = COUNTROWS('Table')
VAR _count_all = CALCULATE(COUNTROWS('Table'),ALLSELECTED())
VAR _result = DIVIDE(_count,_count_all)
RETURN
_result

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

  • Raj12's avatar
    Raj12
    Icon for Helper III rankHelper III
    3 years ago

    Can anyone please help me out on this?
    Thanks