Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Using New Measure, SUM all Defects with Filter for Fiscal Year

New to Power BI, just trying to navigate my way around.....
 
I tried the new measure below in Table Data unsuccessfully.
 
FY18 Total by Defect = Calculate(Sum(Data[Amount]), Filter(Data,Data[Date]) = dDate,dDate[FiscalYear]= "FY18")
 
I do have a one to many relationship from the Table dDate[Date] to Table Data[Date], thus i thought the above would work.
 
Ideally i want to determine for each Defect type (in table Data[Defect]) a total for each Defect by Fiscal Year. But first i must get past the Fiscal Year obstacle.
 
I also played around with adding several New Columns in another table called DefectType where i list each Defect type (unique defect types only). In this case for each Fiscal Year FY18, FY19 & FY20 (in seperate columns) i would sum the amount for each Defect type using the amount in Table Data[Amount].Again i have a one to many relationship from Table DefectType[Defect] to Table Data[Defect].
 
I need some assistance to get the best approach to solving this.
 
Thanks
 
 

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

    I can think about pivot column feature and ALLEXCEPT() function but we need some sample data to confirm.

    Can you show some sample data and the result you want to us if you don't have Confidential Information?

     

    Best Regards,

    Jay

    Community Support Team _ Jay Wang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I created a sample file, only to realize that I can't upload, company doesn't permit Dropbox etc.

     

    The sample file i created relationships are shown below.

    RelationshipsRevenue table

    dDate table

    Relationships..

    Models table to Sales table in linked via Model

    dDate table is linked to Sales table via Date

    Revenue table is linked to dDate table via Fiscal Year

     

    Sales table

    The Revenue table is shown detailing for each FY total units sold and total revenue

    The Sales table contains the many observations.

    The Sales table is also shown above, with 5 columns Date, Model Manufacturer, Qty & Amount

     

    My ultimate require would be to show a clusterbar chart where for each Fiscal Year we would show the normalized % of total sales by Model / FY Revenue. As an expansion to this i would probably do the same for Manufacturer / FT Revenue.

     

    For example let's say we have sales of Corrola's at $3,889,692 for FY20, with FY20 total revenue at $21,421907 results in 18.1%

     

    I did add captions to the images so i hope they showup.

     

    Thanks