Forum Discussion

jhd's avatar
jhd
Helper I
8 years ago

Fiscal Year Total

Below is a "simple" version of my data table: 

 

DateMonthProductSales_ValueFinancial_YearFY_Sales_Value
01/02/2018FebA50020171100
02/02/2018FebA60020171100
01/03/2017MarA4002016400
01/02/2018FebB7002017700

 

I calculate the column FY_Sales_Value

sumx(

filter(table1, Table1[Product]=earlier(Table1[Product]) &&

Table1[Financial_Year]=earlier(Table1[Financial_Year])), 

 Table1[Sales_Value])

 

The problem comes when I put this data into PowerPivot because I'm generally not interested in specific dates, just Months. The FY_Sales_Value then gets multiplied by the number of lines in the table per month.

 

I am therefore thinking that it is better to work out the FY_Sales_Value as a PowerPivot Measure, but I can't figure out how to do this. Hopefully someone can point me in the right direction.

 

Thanks.

Need Help

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    Hi jhd,

     

    In Power BI desktop, after adding calculated column [FY_Sales_Value] into a table visual, to avoid it to be multiplied by the number of lines in the table per month, you can select "Don't summarize".

     

    However, if you have any concern about using PowerPivot, I would suggest you post question on a more related forum.

     

    Best regards,

    Yuliana Gu