topic Re: Multiplying Single Values by A Measured Column Total in Table in DAX Commands and Tips

Multiplying Single Values by A Measured Column Total in Table

Anonymous — Wed, 10 Jul 2019 15:19:36 GMT

I have numeric values (1,2,3,4,etc) in one column, and I want to multiply each of these values by the total of a different, measured column. Is this possible within Power BI?

For example, in Power BI, I utilized the distinct count formula to give me the distinct count of shipments ( "Distinct Count (Y)" ). I also have basic column that gives me the total cost of that shipment ( "X" ). Then, I created a measure that divided Total Cost by the Distinct Count of Shipments ( "X/Distinct Count (Y)" ).

Now, I have another column that has difference in the distinct count of shipments between this month and last month ( "Z" ). My goal is to multiple each of the values in "Z" by the total of "X/Distinct Count Y". The total of "X/Distinct Count Y" is basically the overall average cost per shipment.

Hope this explanation helps. I created this example in excel to visualize what I am looking to accomplish.

Re: Multiplying Single Values by A Measured Column Total in Table

Anonymous — Wed, 10 Jul 2019 14:21:47 GMT

Mate, if you were to read what you've written, would you understand it? Don't be surprised that you don't get any answers...

Best

Darek

Re: Multiplying Single Values by A Measured Column Total in Table

Anonymous — Wed, 10 Jul 2019 15:37:00 GMT

Hi,

I think I understand what you mean. I assume that you have either a product that you are shipping or a ship that is doing the shipping for which you have based the distinct count on? You seem to be missing a column in your table/visualisation. Anyway, if you apply an ALL() filter on that column, would this return you the Distinct Count Total?

For example, if the column that you are applying distinct count on is [ProductCode] then you could use:

Overall Average Cost:=CALCULATE([X/Distinct Count (Y)],ALL([ProductCode]))

With your Goal column then just multiplying distinct count Y by your overall average cost.

I think!