Forum Discussion

jrp420's avatar
jrp420
Regular Visitor
9 years ago
Solved

Creating a total on a measure

Hey all, I'm just getting started with Power BI and have run into an issue I'm trying to solve. I've created a measure that is multiplying the gross AR balance with the % change in AR reserves. This ...
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi jrp420,

    A measure is a formula that is created specifically for numeric data that you want to summarize or analyze report. Measures can be based on standard aggregation functions, such as COUNT or SUM, or you can define your own formula by using DAX. When you create a measure, when add the measure in a table visual. The measure will calculate Sum Gross AR balance and Sum of AR Reserve % Pr Wk Change firstly, then it will multiple.

    For your requriement, you should first create calculated column like Phil_Seamark posted. I test it and get expected result.

    calculted Column = Test1[Gross AR Balance]*Test1[AR Reearves % Pr WK change]

     



    Then add the calculated column to table visual. The "Test1 measure" is a mesure likes yours, please review in the following screenshot.



    If you have other issues, please feel free to ask.

    Best Regards,
    Angelia







jrp420's avatar
jrp420
Regular Visitor
9 years ago

Yes, that is exactly the current measure that I'm using:

 

AR New Specific Reserve = 'Billed + WIP + Write-Offs + Reserves'[Gross AR Balance]*'Billed + WIP + Write-Offs + Reserves'[AR Reserves % Pr WK Change]

 

The issue is that the total row is calulating $956K (Sum Gross AR balance) * 100% (Sum of AR Reserve % Pr Wk Change) = $956.

 

I would rather get a new AR New Specific Reserve measure that totals everything above it, which would sum to $195K. Hopefully that makes sense.

 

 

v-huizhn-msft's avatar
v-huizhn-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi jrp420,

A measure is a formula that is created specifically for numeric data that you want to summarize or analyze report. Measures can be based on standard aggregation functions, such as COUNT or SUM, or you can define your own formula by using DAX. When you create a measure, when add the measure in a table visual. The measure will calculate Sum Gross AR balance and Sum of AR Reserve % Pr Wk Change firstly, then it will multiple.

For your requriement, you should first create calculated column like Phil_Seamark posted. I test it and get expected result.

calculted Column = Test1[Gross AR Balance]*Test1[AR Reearves % Pr WK change]

 



Then add the calculated column to table visual. The "Test1 measure" is a mesure likes yours, please review in the following screenshot.



If you have other issues, please feel free to ask.

Best Regards,
Angelia







  • jrp420's avatar
    jrp420
    Regular Visitor
    9 years ago

    Thanks! I understand now.