Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
% 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])
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.
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] ) )
tRY this one
= SUM ( TableNmae[qty] ) / CALCULATE ( SUM ( TableNmae[qty] ), ALL ( TableName[Type] ) )
his should definitely work
or your own MEASURE with this revision
= SUM ( TableName[qty] ) / SUMX ( VALUES ( datetable[date] ), CALCULATE ( SUM ( TableName[qty] ) ) )
thx @Zubair_Muhammad!
but both your suggestion return 1, which is not what i intended. 😞
HI @joiecy
It gives me correct results... I will try to upload the file
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |