cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jrp420
Regular Visitor

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 works as intended, but the total line is meaningless. I would rather have a total that simply sums the total AR New Specific Reserve column shown below (195,324). 

 

Any help would be greatly appreciated. 

 

Capture.JPG

 

 

1 ACCEPTED SOLUTION

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]

 

1.PNG

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

2.PNG


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

Best Regards,
Angelia







View solution in original post

6 REPLIES 6
Phil_Seamark
Microsoft
Microsoft

Hi @jrp420,

 

Would you prefer the total row was blank for this column?  Or what should the value be?

 

We can definitely get this going for you


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Thanks for the quick follow-up. Ideally, I would like to create some kind of way to sum the "AR New Specific Reserve" measure output. So based on my screenshot, the total would be 195,324 rather than 956,082. The 956,082 is calculating total gross balance * total AR Reserve % Pr Wk Change, which doesn't make sense for the aggregate.

Have you tried the following Calulated Column?

 

AR New Specific Reserve = 'Table1'[Gross AR Balance] * Table1[AR Reserves % Pr Wk Change]

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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.

 

 

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]

 

1.PNG

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

2.PNG


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

Best Regards,
Angelia







Thanks! I understand now. 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.