Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SachinC
Helper V
Helper V

DAX Formula Help Working Out Percentages

Hi

I have a calculation dilemma if someone can help.

I have a data grid with the following columns.

My report has a date filter called DistributionDate. The more dates I select, i.e. 02/11/19, 03/11/19 it increments the count of CountofDistDate.

What I need to work out is the % of Returns / Drops regardless of dates selected. If I drop into my view the DistributionDate field, everything works as it should. However, when I take the field out, it should work out the % but it isn’t.

Here is my formula: % = DIVIDE(SUM('View_1BBB'[Percentage]), COUNTA('View_1BBB'[RouteID]))

Without DistrubtionDate (note % is wrong because I am dividing Percentage by CounofDisDate):

DisplayedRouteID

Returns

TotalDrops

Percentage

CountOfDistDate

%

LD001

44

120

111.1111

3

37.03704

LD002

76

125

131.4843

3

43.82811

LD003

59

99

176.8519

3

58.95062

LD004

89

175

203.3283

4

50.83209

 

With DistributionDate:

DisplayedRouteID

Returns

TotalDrops

DistributionDate

Percentage

CountOfDistDate

%

LD001

24

42

01/11/2019 00:00

57.14286

1

57.14286

LD001

4

42

02/11/2019 00:00

9.52381

1

9.52381

LD001

16

36

03/11/2019 00:00

44.44444

1

44.44444

LD002

1

33

29/10/2019 00:00

3.030303

1

3.030303

LD002

1

33

30/10/2019 00:00

3.030303

1

3.030303

LD002

74

59

04/11/2019 00:00

125.4237

1

125.4237

LD003

24

36

30/10/2019 00:00

66.66667

1

66.66667

LD003

21

36

02/11/2019 00:00

58.33333

1

58.33333

LD003

14

27

03/11/2019 00:00

51.85185

1

51.85185

LD004

33

41

30/10/2019 00:00

80.4878

1

80.4878

LD004

30

41

01/11/2019 00:00

73.17073

1

73.17073

LD004

1

40

02/11/2019 00:00

2.5

1

2.5

LD004

25

53

04/11/2019 00:00

47.16981

1

47.16981

 

I want to show the grid without the DistributionDate column.

How do I get my formula to work? Please help.

Thanks.

 

1 ACCEPTED SOLUTION
sturlaws
Resident Rockstar
Resident Rockstar

Hi @SachinC ,

 

so you want your table to look exactly like your second table, but without the distribution date?

 

Cheers,
Sturla

View solution in original post

2 REPLIES 2
sturlaws
Resident Rockstar
Resident Rockstar

Hi @SachinC ,

 

so you want your table to look exactly like your second table, but without the distribution date?

 

Cheers,
Sturla

Sorted. I made a silly mistake. Not to worry. Thanks.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.