Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello everybody!
I´m bogged, trying to solve a simple problem. I have a "Sales" table with info such as this:
Order | SKU | Quantity |
1 | Banana | 10 |
1 | Apple | 5 |
2 | Banana | 10 |
2 | Apple | 1 |
3 | Banana | 10 |
3 | Apple | 5 |
4 | Banana | 10 |
5 | Apple | 2 |
I need to generate a grand total for Bananas, a grand total for Apples, and then divide Apples/Banannas.
In this example:
GT Bananas = 40
GT Apples = 13
Ratio = 13 / 40 = 32,5%
I use this method to compare the sales of a "weak" product (apples) vs the sales of a "strong" product (bananas) and to find new opportunities to cross-sell.
Any helping hand/brain?
Solved! Go to Solution.
Measure 1:
GT Apples = CALCULATE( SUM( 'Sales'[Quantity] ) , 'Sales'[SKU] = "Apples" )
Measure 2:
GT Bananas = CALCULATE( SUM( 'Sales'[Quantity] ) , 'Sales'[SKU] = "Bananas" )
Measure 3:
Apple % of Banana = [GT Apples] / [GT Bananas]
Or, you could combine into one measure like this:
Apple % of Banana =
VAR
GT_Apples = CALCULATE( SUM( 'Sales'[Quantity] ) , 'Sales'[SKU] = "Apples" )
GT_Bananas = CALCULATE( SUM( 'Sales'[Quantity] ) , 'Sales'[SKU] = "Bananas" )
RETURN
GT_Apples / GT_Bananas
Hi @jppasini
Create measures
total per sku = CALCULATE(SUM('Table'[Quantity]),ALLEXCEPT('Table','Table'[SKU]))
weak/strong =
VAR min_ =
MINX ( ALL ( 'Table'[SKU] ), [total per sku] )
VAR max_ =
MAXX ( ALL ( 'Table'[SKU] ), [total per sku] )
RETURN
SWITCH ( [total per sku], min_, "weak", max_, "strong" )
ratio =
CALCULATE (
[total per sku],
FILTER ( ALLSELECTED ( 'Table' ), [weak/strong] = "weak" )
)
/ CALCULATE (
[total per sku],
FILTER ( ALLSELECTED ( 'Table' ), [weak/strong] = "strong" )
)
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Measure 1:
GT Apples = CALCULATE( SUM( 'Sales'[Quantity] ) , 'Sales'[SKU] = "Apples" )
Measure 2:
GT Bananas = CALCULATE( SUM( 'Sales'[Quantity] ) , 'Sales'[SKU] = "Bananas" )
Measure 3:
Apple % of Banana = [GT Apples] / [GT Bananas]
Or, you could combine into one measure like this:
Apple % of Banana =
VAR
GT_Apples = CALCULATE( SUM( 'Sales'[Quantity] ) , 'Sales'[SKU] = "Apples" )
GT_Bananas = CALCULATE( SUM( 'Sales'[Quantity] ) , 'Sales'[SKU] = "Bananas" )
RETURN
GT_Apples / GT_Bananas
hi @jppasini
try a measure
Measure = divide(calculate(sum('Table1'[Quantity]);ALL('Table1');'Table1'[SKU]="Banana");calculate(sum('Table1'[Quantity]);ALL('Table1');'Table1'[SKU]="Apple"))
do not hesitate to give a kudo to useful posts and mark solutions as solution
User | Count |
---|---|
96 | |
85 | |
77 | |
66 | |
63 |
User | Count |
---|---|
110 | |
96 | |
96 | |
67 | |
59 |