CODEWITHSUNDEEP
powerbidaxdata-analysispowerbi-desktop
R_Student
R_Student

Reputation: 789

Calculating running count and percentages from long-type data

I have a dataset that looks like so:

ID_SALE PRODUCT STORE
SE_056 AAA NORTH
XT-558 AAA NORTH
8547Y AAA NORTH
TY856 BBB NORTH
D-895 BBB SOUTH
ER5H CCC SOUTH
5F6F-GD CCC SOUTH
65-FFD TTT SOUTH
56-YU UUU SOUTH

I want to be able to plot a table that will show the count of each PRODUCT and the contribution of the global percentage of each PRODUCT as well as the cumulative percentage like so:

PRODUCT Subtotal Percentage running %
AAA 3 0,33333333 0,33333333
BBB 2 0,22222222 0,55555556
CCC 2 0,22222222 0,77777778
TTT 1 0,11111111 0,88888889
UUU 1 0,11111111 1

I also want to be able to have a filter in the PowerBI sheet that will filter by STORE so if I choose "NORTH" my table will show the following:

PRODUCT Subtotal Percentage running %
AAA 3 0,75 0,75
BBB 1 0,25 1

Although I have used the quick-measure feature to get the cumulative total I get get it to sort in order my data and so I figured that DAX is the only way.

Upvotes: 1

Views: 40

Answers (1)

davidebacci
davidebacci

Reputation: 30289

enter image description here

Assuming your table is named "Table".

Subtotal = COUNTROWS('Table')

Percentage = [Subtotal]/CALCULATE(COUNTROWS('Table'),REMOVEFILTERS())

running % = 
VAR cursor = MAX('Table'[PRODUCT])
RETURN 
CALCULATE( [Percentage], REMOVEFILTERS(),'Table'[PRODUCT]<= cursor)

Upvotes: 1

Related Questions