Forum Discussion

LadyCat's avatar
LadyCat
Frequent Visitor
3 years ago

Two Columns and Multiplication in a Matrix?

Hello, 

 

I need help with the following, which I have been battling with for some time. 

I basically have data that looks like this: 

 

The rows in yellow are the most recent transactions per product and customer ID that I need to display in a matrix. So far, I have been able to do this and it is giving me what I need: 

 

 
The code that I am using for this is :
 
Latest =
VAR LatestDateOfData =
CALCULATE(
MAX(Data[Reporting Date]),
FILTER(Data,Data[Customer ID])
)
RETURN
CALCULATE(SUMX(Data, Data[Value]), FILTER(Data, Data[Reporting Date]=LatestDateOfData)
)
 
And - 
LatestData =
VAR TempTable = SUMMARIZE(Data,Data[Customer ID],"ValueLatest",'Key Measures'[Latest])
RETURN
IF(HASONEVALUE(Data[Value]),'Key Measures'[Latest],SUMX(TempTable,[ValueLatest]))
 

The problem is that I would like to add next to each product the costs displayed in the Excel sheet above and then multiply each value * costs% in a new column. These costs change every reporting period so that there can be several lines for each product and customer. But when I try to pull up the costs column into a visual, Power BI returns all the costs that are in the data model and the total is wrong. So for example, for customer 22 I am getting the following: 

 

 

Instead of just this: 

Next I would like to display 280.488 * 1,73% = 4852,44. 

 

Is there any way I can do this? I tried to create a summary table with two columns - value and costs - but it is not working (otherwise I wouldn't be here).

 

Thank you for your help.

 

 

3 Replies

    • LadyCat's avatar
      LadyCat
      Frequent Visitor

      Thank you so much for this, it works ! 

       

      I have another question if you don't mind? How would you handle having the same product reported on the same day but with different amounts and with different costs? I mean the following - 

      Many thanks

  • Valueproducts=
    VAR t1='DATA'[CutomerID]
    VAR t2='DATA'[Product Number]

    VAR t3='DATA'[CutomerID]

    FILTER(LatestDate=MAX[ReportingDate] && t1=t3,t2)

    RETURN

    SUMX(SUMMARIZE(DATA[Value],FILTER(DATA[LatestDate], DATA[Value]*DATA[Costs])))