Forum Discussion

Vitor_sp_bo's avatar
Vitor_sp_bo
Icon for Helper I rankHelper I
4 years ago

Calculation

Guys, good morning! I have a sales table with two years being 2020 and 2021, I need to do the following calculations 2021 product sales amount * 2021 product sales amount 2020 product sales amount * ...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
4 years ago

Vitor_sp_bo Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Vitor_sp_bo's avatar
Vitor_sp_bo
Icon for Helper I rankHelper I
4 years ago

Greg_Deckler 

Okay, I'll try to make a logical explanation and I'm attaching an example of how I did it in excel

 

 


1° Step
2020 product sale value * Number of products sold - 2021

2° Step
2021 product sale value * Number of products sold - 2021


3° Add the total of products after the multiplication ( Separated by year, each year has its sum)

4°
Divide the sum of 2021 by the sum of 2020 - 1

I can only consider products that are in both years,


The value I need is the one in yellow:

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Try DIVEDE function:

    Measure = DIVIDE(SUM('Table'[2021 sales]),SUM('Table'[2020 sales]))-1

    If you provide aggregated data, it is difficult to know your data model. Please provide the original data model in pbix with virtual data.