cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

Table does not add up to the total value

Good afternoon friends,

I have a problem, I made a report in Power Bi that was fine, but now they asked me to show the information of last year versus the information that is generated this year, I am starting to work on that and ps the only thing that occurred to me is that to the generated calculations I add a filter specifying the year 2021 or 2022 respectively as the case may be, but it turns out that now I inserted a table with last year's production and this year's production, but in those rows where it shows the production does not calculate the totals, the other rows where I put the total amount and the areas if it adds me, only in those rows that have to do with the production does not add up, It should be noted that the production I get from another table but I can not relate it to the cost table since both have values that are repeated, only both tables have another table in common related, but the information itself pulls them from the cost tab and the projection table, the formula I used to calculate this projection is as follows:

Kg_Eje 21 Va = CALCULATE(SUM(tbl_Proyeccion[BRUTO]),FILTER(tbl_Proyeccion,tbl_Proyeccion[AÑO]=2021),FILTER(tbl_Proyeccion,tbl_Proyeccion[ETAPA]=SELECTEDVALUE(tbl_Costo[VARIEDAD])),FILTER(tbl_Proyeccion,tbl_Proyeccion[CCOSTO]=SELECTEDVALUE(tbl_Costo[CCOSTO])))

How could I get him to show me the totals?

I attach the project in case you need it, sorry for all the mess but I am just elaborating it, of course I am very grateful for your time and I hope you can help me in the resolution of this small inconvenience.

Best regards.

Field Costs

1 ACCEPTED SOLUTION
v-xiaotang
Community Support
Community Support

Hi @Syndicate_Admin 

the formula you used to calculate that projection is wrong, you can try the modified version bellow,

Kg_Eje 21 Va =
CALCULATE (
    SUM ( tbl_Proyeccion[BRUTO] ),
    FILTER (
        ALL ( tbl_Proyeccion ),
        tbl_Proyeccion[AÑO] = 2021
            && tbl_Proyeccion[ETAPA] = SELECTEDVALUE ( tbl_Costo[VARIEDAD] )
            && tbl_Proyeccion[CCOSTO] = SELECTEDVALUE ( tbl_Costo[CCOSTO] )
    )
)

I also find related articles, please see link1,and link2, will they help?

 

 

 

Best Regards,

Community Support Team _Tang

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

View solution in original post

5 REPLIES 5
v-xiaotang
Community Support
Community Support

Hi @Syndicate_Admin 

the formula you used to calculate that projection is wrong, you can try the modified version bellow,

Kg_Eje 21 Va =
CALCULATE (
    SUM ( tbl_Proyeccion[BRUTO] ),
    FILTER (
        ALL ( tbl_Proyeccion ),
        tbl_Proyeccion[AÑO] = 2021
            && tbl_Proyeccion[ETAPA] = SELECTEDVALUE ( tbl_Costo[VARIEDAD] )
            && tbl_Proyeccion[CCOSTO] = SELECTEDVALUE ( tbl_Costo[CCOSTO] )
    )
)

I also find related articles, please see link1,and link2, will they help?

 

 

 

Best Regards,

Community Support Team _Tang

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

Thank you very much friend, that formula worked perfectly

elofstrom
Resolver II
Resolver II

The solution should still be relatively easy - the Date dimension will help you solve that.  You can assign any date to a "campaign year".  Thus while 01 Nov 2020 may exist in calendar year 2020 you could assign it to campaign year 2021 by creating a separate campaign year column in the date dimension.

Calendar Date Dimensions | Kimball Dimensional Modeling Techniques (kimballgroup.com)

elofstrom
Resolver II
Resolver II

It looks like the simple answer is that you are missing a Date dimension.

Create date tables in Power BI Desktop - Power BI | Microsoft Docs

By creating a Date dimension (similar to the Situacion table) and joining the tables to that on Date likely will solve your issue.  Then, instead of hard-coding 2021 and 2022 in the DAX you'd use the Date dimension to control the filtering and totaling.

 

Thank you very much for your prompt response, the small drawback is that the years 2021 and 2022 do not refer to chronological years, if they are not the names of the campaigns, for example the 2021 campaign includes from October 2020 to mid-December 2021 and the 2022 campaign comprises from mid-December 2021 to January 2023 possibly

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors