Forum Discussion

mkoontz's avatar
mkoontz
Frequent Visitor
5 years ago
Solved

Total for measure column

Power BI 101 question here. Need help getting the correct total to show in a column that is a measure.

 

Measure 1 = SUM(TABLE1 [Project_Actual])

Measure 2 (to get Measure 1 from previous month) =

CALCULATE( [Measure 1],
FILTER( ALL( dimDate ),
dimDate[Period Rank] = MAX(dimDate[Period Rank]) - 1)
)

Measure 3 = [Measure 2] - [Measure 1]

 

When I export out to Excel and SUM up the column, the total looks great for Measure 3.

But in my Power BI report, it is just taking the difference between Measure 1 Total and Measure 2 Total instead of summing the column. Again, probably very basic, but I can't seem to get it.

Any help would be greatly appreciated. Thank you.

Need Help
  • mkoontz's avatar
    mkoontz
    5 years ago

    I was able to fix this using the VALUES function you mentioned above, but just making a few changes. Thank you for the direction you gave me. I appreciate it.

     

    SUMX(
    Measure 3 =
    VALUES(dimProject[Project Id]),
    IF([Measure 1] = BLANK(), 0, [Measure 2] - [Measure 1])
    )

4 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    mkoontz 

    Can you try the following for Measure 3?

    Measure 3 =
SUMX( 
    VALUES(dimDate[Period Rank]),
    [Measure 2] - [Measure 1]
)
    • mkoontz's avatar
      mkoontz
      Frequent Visitor
      5 years ago

      Hi Fowmy,

      Thank you for the quick reply. Unfortunately that did not work. 

       

      Some more details on the report layout.

      Project Id (dimProject)

      8 measures (including the 3 above) from 2 different Fact Tables with relationships setup back to dimProject

      • Fowmy's avatar
        Fowmy
        Icon for Super User rankSuper User
        5 years ago

        mkoontz 

        Sharing a sample PBIX file will help understand the model.
        You save it in google drive or one drive and share the link here.