Forum Discussion

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
10 years ago

Dealing with Measure Totals

This one has come up quite a bit recently. The issue surrounds using Measures in Table visualizations with a Total row. The complaint is that the "Total" row is "wrong" for the measure. Technically, ...
mgiusto's avatar
mgiusto
Icon for Helper I rankHelper I
5 years ago

I have a different issue with measures that I can't seem to figure out. I have a drop-down selector for a user to choose an Estimated Cost, I then refer to this in a measure like so: SelectedCost = SELECTEDVALUE(PerEntryCost[EstimatedCost])

 

What I want to do next is divide this SelectedCost by the TotalEntries field in my grid below, I want to divide it by the 122 which is the total. Then when I have that value (which is 9.84) I want to use that 9.84 to multiply against the 92 in the first row and have my EstCost field show 905.28 and the second row multiply 9.84 by 30 to get 295.20. Then my final total of EstCost on the bottom row would show $1,200.00. 

 

Here are my measures:

SelectedCost = SELECTEDVALUE(PerEntryCost[EstimatedCost])

Total Entries = DISTINCTCOUNT(CHB_Declarations[CHBFileID])

PerEntryCost = [SelectedCost]/[Total Entries]

EstCost = [Total Entries]*[PerEntryCost]

 

As you can see below, the measure is being calculated at each row level not using the total of Total Entries, same goes for the PerEntryCost, I am expecting that to be 9.84 on each row. (I'm not going to be showing the per entry cost in the final solution, I just put it there to show the issue I am experiencing.) I really just need the EstCost to work the way I have described above.

 

Some more deatil on this table view, this is based on a couple slicers of a date range and one customer, so if these filters are changed I need these measures to recalculate based on the new slicer selections.

 

I'm thinking this is probably pretty easy, I am just having a hard time figuring it out. I tried calculated columns and that didn't work either.  Thanks for any assistance!

 

Michael Giusto

Greg_Deckler Ashish_Mathur 

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

    Hi,

    Edit the Total entries measure to:

    Total Entries = calculate(DISTINCTCOUNT(CHB_Declarations[CHBFileID]),all(CHB_Declarations[broker_name]),all(CHB_Declarations[branch_name]))

    Does this help?

    • mgiusto's avatar
      mgiusto
      Icon for Helper I rankHelper I
      5 years ago

      Ashish, 

       

      This worked but I had to make it a separate [Total Entires] measure as it affected my Total Entries in my grid and made both rows 122. I created another measure with your DAX and edited the PerEntryCost measure to that new measure and everything works great!  Thank you so much!

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

        You are welcome.  If my reply helped, please mark it as Answer.