How to divide each row of a calculated column by t

问题:

I can't get a division correct with this sample data:

Calculated column    Another calc. column
   48                  207
  257                  370
  518                  138
  489                  354
  837                  478
1,005                  648
1,021                2,060
1,463                2,164
2,630                1,818
2,993                2,358
3,354                3,633
4,332                5,234
4,885                6,108
4,514                6,008
4,356                6,888
4,824                7,382
7,082                5,988
7,498                6,059
                     4,865
                     4,192
                     3,816
                     2,851
                     2,768
                     2,093
                     2,207
                       770
                       397
                       149
                       178
                       336
                       167
                       124
                        18

What I'm trying to do is to create a new calculated column.

For each row I want to get the value of Calculated column and divide it by the Total of Another calc. column.

The Total of Another calc. column = 82826

This is the desired output in a brand new calculated column, let's call it % Column:

% Column

0,000579528167484
0,003102890396735
0,006254074807428
.
.
.

NOTE - these 3 columns: Calculated column, Another calc. column and % Column are all in the same table and are all calculated columns.

I tried lots of formulas but not a single one returned the desired output. :| I guess it's because of the nature of calculated columns or I'm not getting the gist of it.

Is this even possible or I should follow another path using a Measure?

Can you shed some light?

####### EDIT #######

I put together a sample file to help debugging this. Here it is: https://drive.google.com/open?id=1r7kiIkwgHnI5GUssJ6KlXBAoeDRISEuC

As you see:

• Earned Daily % HARDCODED works just fine because 82826 is hardcoded as the denominator.
• Earned Daily % by StelioK and Earned Daily % by Alexis Olson output the same wrong value for the division when using SUM formula.

I'm using the latest Power BI Desktop version if that matters: Version: 2.70.5494.701 64-bit (June 2019)

回答1:

Basically, there is nothing wrong with the calculated columns, and both Alexis and StelioK formulas are correct.

The root problem here is a confusion between calculated columns and measures. You are looking at the results in a conceptually wrong way - through the matrix visual, with several filters active on slicers. If you remove the filters, you will see that the total amount is 140,920, not 82,826. The latter number is the total for the filtered data set, not the entire table.

To get this right, you need to understand several fundamental concepts behind Power BI:

• Calculated columns are always static. Once a calculation is completed, it can not respond to slicers or other UI controls. It's just static data, identical to data in non-calculated columns. DAX formulas used to calculate columns are active only when you create them, or upon data reload.
• If you want your calculations to respond to slicers etc, they must be measures. It's the only way, no exceptions.
• Avoid calculated columns, they are utterly useless. Power BI is all about measures; I can't think of a single reason for using calculated columns. When you add a column, you are essentially enhancing your source data, because you feel like you are missing something you need for your report. But that need can be much better addressed at the source (database or file you import), or using Power Query, which is designed exactly for this kind of tasks. The best practice is: build your columns at the source, for everything else design measures.
• Another important advice: never drop fields (columns) into visuals directly. Always write a DAX measure, and then use it. Relying on Power BI auto-aggregations is a very bad practice.

回答2:

You can do this by using the following DAX:

% Column =
VAR TotalSum =
    SUM ( 'Table'[Another Calc column] )
RETURN
    IF (
        NOT ( ISBLANK ( 'Table'[Calc Column] ) ),
        CALCULATE ( DIVIDE ( SUM ( 'Table'[Calc Column] ), TotalSum ) ),
        0
    )

Which yields the following:

I Hope it helps!!

回答3:

For me the following works:

DIVIDE( Table1[Calculated column], SUM(Table1[Another calc column]) )

If that's not working, I'd need to see a file where you can reproduce the problem.

---

Edit: After looking at your file, the total of 82,826 is only true with the filters you've selected.

Note that calculated columns are not dynamic and cannot be responsive to filters since they are calculated only when the table is first loaded.

If you need it to be dynamic, then write it as a measure more like this:

Earned Daily =
DIVIDE (
    CALCULATE (
        SUM ( 'Test data'[Value] ),
        'Test data'[Act Rem] = "Actual Units",
        'Test data'[Type] = "Current"
    ),
    CALCULATE (
        SUM ( 'Test data'[Value] ),
        ALLSELECTED ( 'Test data' ),
        'Test data'[Act Rem] = "Remaining Units",
        'Test data'[Type] = "PMB"
    )
)