Forum Discussion

4-3-3's avatar
4-3-3
Helper I
6 years ago

Help with virtual table/advanced summarize?

Hey team, I facing the following scenario. I have financial data of receipts and invoices for our customers and I need to figure out the amount of 'active debtors' at the end of each week.   I am a...
Need Help
4-3-3's avatar
4-3-3
Helper I
6 years ago

Wicked Amit. Almost there! Would you be able to adjust it so it shows the amount per customer, and only the values higher than 0? Like in this screenshot?

https://i.imgur.com/3ZDYYsk.png

v-kelly-msft's avatar
v-kelly-msft
Community Support
6 years ago

Hi 4-3-3 ，

 

Your measure needs to be modified as below:

 

 

 

debtAmit = CALCULATE(SUM(Data[Transaction Amount]), FILTER(Data, Data[Effective] <= MAX('Week Calendar'[Week End])  && Data[Leave Date] >MAX('Week Calendar'[Week End])))

 

 

 

Then create a measure:

 

 

Measure = SUMX(DISTINCT('Data'[Customer ID]),CALCULATE(IF([debtAmit]<=0,BLANK(),[debtAmit])))

 

 

 

Finally you will see:

 

For the related .pbix file,pls click here.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

 

 

 

  • 4-3-3's avatar
    4-3-3
    Helper I
    6 years ago

    Hi Kelly,

    thanks for trying to help. Unfortunately this is not quite the solution I am after. What I am after is to get the 'debtAmit' and 'Measure' to match the same value (1566.90). The 'debtAmit' measure in your attempt is still listing all transactions, even the ones <0 and that is why it is only showing 810.15

     

    Can you please have a look at it again?