Display Calculated Measure Bins as Axis, Calculated Measure as Values

[Anonymous]

Team - 

I have a table of weekly data (ProductData) and I would like to display weighted profit for Product A by it's gap to Product B (sample data below):

Customer	Product	Price Per Unit	Profit	Dollar Sales	Week
Customer A	Product A	1.99	1.00	50,000	Week 1
Customer A	Product B	1.89	1.50	60,000	Week 1
Customer B	Product A	1.79	1.10	1,000,000	Week 2
Customer B	Product B	1.66	1.60	2,500,000	Week 2

I am comparing the price of the two products based on user parameter selections - the parameter table calculation is as follows:

Parameters = 

var products = VALUES('ProductData'[Product])

return CROSSJOIN(

SELECTCOLUMNS(products,"Product 1", [Product]),

SELECTCOLUMNS(products,"Product 2", [Product])

)

After the user selects two different products - I have the below measure that calculates the price gap between the two products:

Prod1-Prod2 Gap =

var prod1 = CALCULATE(

SUM('ProductData'[Price Per Unit]),

TREATAS(VALUES(Parameters[Product 1]),'ProductData'[Product])

)

var prod2 = CALCULATE(

SUM('ProductData'[Price Per Unit]),

TREATAS(VALUES(Parameters[Product 2]),'ProductData'[Product])

)

return (prod1-prod2)/prod2

I want to treat each result as a separate observations and bin them in 5% intervals - using the below calculation:

Gap Bins =

VAR BinSize = 0.05

VAR Bin = INT('ProductData'[Prod1-Prod2 Gap] / BinSize) * BinSize

RETURN Bin

I then weigh the profit by dollars sales at each customer - using the below calculation

Weighted Profit =

SUMX('ProductData',('ProductData'[Profit]*'ProductData'[Dollar Sales])/'ProductData'[Dollar Sales])

I now want to display Weighted Profit by Gap Bins in a bar chart (example below) - the issue I am running into is that you cannot use a calculated measure as an axis.  I also fear that the Weighted Profit calculation will bring in Profit for both Product A and Product B when all I care about is Product A profit.

I am more versed in Tableau than Power BI so, I will appreciate any help that you could provide! 😅

[v-diye-msft]

Hi,

If you've fixed the issue on your own please kindly share your solution. if the above posts help, please kindly mark it as a solution to help others find it more quickly.thanks!

[Greg_Deckler]

Power BI does support the concept of binning. It is found under the context menu of a column and you choose Group. Then you can select the grouping type as Bin.

However, in your case, you might want to create a new table to serve as your axis using something like:

Table 2 = GENERATESERIES(0,100,.05)

Then you could use that as your axis and write your measure such that it grabs the MAX Value out of that table and then filters your data such that you are only returning things that fall into that each bin.

[Anonymous]

Thank you, @Greg_Deckler -

RE: "Then you could use that as your axis and write your measure such that it grabs the MAX Value out of that table and then filters your data such that you are only returning things that fall into that each bin."

What would be the syntax to reference the Prod1-Prod2 Gap measure results to the values in the disconnected series table?  Would I need to have a MIN/MAX for each line in the generated series?  Not sure I understand how to return values specific to each bin.