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.
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
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 |
---|---|
105 | |
105 | |
88 | |
73 | |
66 |
User | Count |
---|---|
122 | |
112 | |
98 | |
79 | |
72 |