DAX measure to get percentage by group

[joiecy]

i wanted to create a measure that can give me the daily rate of qty.

i have a table example like this:

DATE	TYPE	QTY
1/1/2018	Fruit	2
1/1/2018	Fruit	3
1/1/2018	Fruit	1
1/1/2018	Vege	4
1/1/2018	Vege	5
1/1/2018	Meat	3
2/1/2018	Vege	4
2/1/2018	Meat	6
2/1/2018	Meat	1
2/1/2018	Meat	5
2/1/2018	Fruit	7
2/1/2018	Fruit	2

I wanted to get their daily rate such that the formula should be something like [sum(qty) of each type of each day] / [sum all (qty) of each day].

result table like this:

DATE	TYPE	DAILY RATE
1/1/2018	Fruit	0.333333333
1/1/2018	Vege	0.5
1/1/2018	Meat	0.166666667
2/1/2018	Fruit	0.36
2/1/2018	Vege	0.16
2/1/2018	Meat	0.48

I tried this but the SUMX is getting the sum all of the entire table instead of each type

=SUM(qty)/SUMX(ALL(table), qty)

this is not working as well

=SUM(qty)/SUMX(VALUE(datetable[date]), qty)

how should i write the DAX formula to achieve this?

[Anonymous]

% of Total Sales:=([Total SalesAmount]) / CALCULATE([Total SalesAmount], ALLSELECTED(Sales[Category],Sales[SubCategory]))

OR 

% of Total Sales:=DIVIDE(([Total SalesAmount]) , CALCULATE([Total SalesAmount], ALLSELECTED(Sales[Category],Sales[SubCategory])))

WHERE Total Sales = SUM('Sales'[SalesAmount])

https://support.office.com/en-us/article/when-to-use-calculated-columns-and-calculated-fields-ca18d6...

[joiecy]

hi guys,

Appreciate your help again.

Extend to this scenario, lets say there are 2 levels of grouping.

Date	Group	TYPE	QTY
1/1/2018	Plant	Fruit	2
1/1/2018	Plant	Fruit	3
1/1/2018	Plant	Fruit	1
1/1/2018	Plant	Vege	4
1/1/2018	Plant	Vege	5
1/1/2018	Animal	Meat	3
2/1/2018	Plant	Vege	4
2/1/2018	Animal	Meat	6
2/1/2018	Animal	Meat	1
2/1/2018	Animal	Meat	5
2/1/2018	Plant	Fruit	7
2/1/2018	Plant	Fruit	2

is there a way where i can have a measure that can calculate the rate no matter i drill up to group or i drill down to type? how to combine this 2 dax?

= SUM ( 'animal and plant'[qty] ) / CALCULATE ( SUM ( 'animal and plant'[qty] ), ALL ( 'animal and plant'[Group] ) )

= SUM ( 'animal and plant'[qty] ) / CALCULATE ( SUM ( 'animal and plant'[qty] ), ALL ( 'animal and plant'[Type] ) )

thank you in advance.

[joiecy]

nevermind guys, i got it already. as simple as just put 2 column in the ALL().

= SUM ( 'animal and plant'[qty] ) / CALCULATE ( SUM ( 'animal and plant'[qty] ), ALL ( 'animal and plant'[Type],  'animal and plant'[group] ) )

[Zubair_Muhammad]

@joiecy

tRY this one

=
SUM ( TableNmae[qty] )
    / CALCULATE ( SUM ( TableNmae[qty] ), ALL ( TableName[Type] ) )

[afzalphatan]

his should definitely work

[Zubair_Muhammad]

@joiecy

or your own MEASURE with this revision

=
SUM ( TableName[qty] )
    / SUMX ( VALUES ( datetable[date] ), CALCULATE ( SUM ( TableName[qty] ) ) )

[joiecy]

thx @Zubair_Muhammad!

but both your suggestion return 1, which is not what i intended. 😞

[Zubair_Muhammad]

HI @joiecy

It gives me correct results... I will try to upload the file

[Zubair_Muhammad]

@joiecy

Please see the attached file here