Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
5 years ago

Add Column Values of a Calculated Measure

Good afternoon

I need to get the total summation of the values in the column "DIF MAGNITUDE. FROM REG. BELOW 30%", which I obtained by implementing a calculated measure, that measure is represented in a table(Figure 1😞

joseignaciodpb_0-1613059021654.jpeg

Figure 1.

This table is linked to a time segmenter(Figure 2😞

joseignaciodpb_1-1613059021655.png

Figure 2.

As I said before, the formula behind the column "DIF MAGNITUDE. FROM REG. BELOW 30%", is a calculated measure(Figure 3😞

joseignaciodpb_2-1613059021656.png

Figure 3.

If I activate the totals in the table configuration section, I get "0" as a result(Figure 4😞

joseignaciodpb_3-1613059021657.png

Figure 4.

Power BI won't let me perform the SUM function on a calculated measure. Thank you very much in advance for any questions or suggestions you may make about it.

2 Replies

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    5 years ago

    There @joseignaciodpb ,

    This is because the total row on Power BI tables isn't a sum of the column values, it is actually your column measures calculated with no filter on them.

    In your scenario, it looks like you would need to create your measures using SUMX i.e. calculate your intermediate measures for each row, then sum the whole lot at the end. This may also require using a virtual table as the table argument in SUMX in order to aggregate values for the SUMX to calculate on.

    Without being able to see your data/model structure I can't help you with the actual calculation, but hopefully this gives you are steer as to where to start looking to solve this issue.

    Pete

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    Hi, Syndicate_Admin 

     

    As is suggested by BA_Pete, you need to add a column on the virtual table and use 'SUMX' function to calculate the sum of the new column.

    Measure = 
SUMX(
    SUMMARIZE(
         Table,
         Table[Column],
         ...,
         'New Column',
         [<logic of your measure>]
    ),
    [New Column]
)

     

    Best Regards

    Allan

     

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