Percentage of category

[yaungwan]

Hi,

I am a first time user and i am struggling bad 😞

I am trying to work out a 'Measure' to find percentage of sum of 'base amount' for 'month' over the each genre.

For example below, the percentage of month 9 for animation is = 1925/(1928+1925+1910)=33%. 

Base Amount	# OF MONTH	TV FILM	GENRE
1,918.00	4	FILM	COMEDY
1,942.00	4	FILM	DRAMA
1,947.00	1	FILM	DRAMA
1,945.00	1	FILM	DRAMA
1,930.00	2	FILM	ACTION
1,915.00	2	FILM	ACTION
1,928.00	10	FILM	ANIMATION
1,925.00	9	FILM	ANIMATION
1,910.00	10	FILM	ANIMATION

Thank you in advance for your help! 

[Anonymous]

Forgive me but I am super novice with DAX. I have a similar dataset but the DAX solution did seems to work out.

Here's my raw data set

System	RunTime
A	5
B	7

I would like to $ diff the run time by System. Like this

System	A	B	% Diff
RunTime	5	7	71%

Any guidance is appreciated

[DHB]

@Anonymous You may have resolved this by now but if not you could add a new column and insert the following calculation...% Diff=100/'Table'[B]*'Table'[A]

You'll need the data in the format of your second table (the one showing the %) for this to work.

[Sean · Accepted solution]

@yaungwan  Create these 3 Measure

Measure 1

Amount Measure = SUM('Table'[Base Amount])

Measure 2

Total Genre Amount = CALCULATE( [Amount Measure], ALLEXCEPT('Table', 'Table'[GENRE]) )

Measure 3

% Measure = DIVIDE ( [Amount Measure], [Total Genre Amount], 0 )

or you can create 1 that does it all

Measure Alternate

% Measure =
DIVIDE (
    SUM ( 'Table'[Base Amount] ),
    CALCULATE ( [Amount Measure], ALLEXCEPT ( 'Table', 'Table'[GENRE] ) ),
    0
)

The first approach is better for the long term - gives you more flexibility to use those Measure later

As Rob Collie says - Never write the same formula twice!

Hope this helps!