Forum Discussion
Calculating Monthly Percentage
I am working on a project to track the utilization of steel at our facility. What I am looking to do is calculate the monthly utilization percentage using the following equation: Total Used Steel (m^2)/Total Steel (m^2). Total Steel is calculated by multiplying the values in the "Quantity" Column and the "Sheet area" Column. Total Used Steel is calculated by multiplying "Total Steel" values and the values in the "Utilization Percent Column. The resulting report will have twelve card visualization, one for each month. Each card will display the utilization percentage for that month. The issue I'm currently having is that the card visualization is requiring me to choose to display it as sum, average, etc.. How do I set this up so the cards only show that months utilization percentage without having to choose sum, average etc?
Need to use the SUM function in the measure. Essentially putting the calculation in the background as opposed to a setting on the card visual as you encountered.
%_Used = SUM(Sheet1[Total_used]) / SUM(Sheet1[Total_Steel])Once I had created those two columns, I created that as a measure (modeled as a %) for the card value.Then you should be able to use visual level filters to set your cards to the appropriate Month.I understand using that Steel # as a filter. That explains it, thank you. I was thinking of it more as a "Job" or Asset number instead of a classification.
3 Replies
Cammarcz,
Create other columns that calculate Total Steel and Total Used Steel. Create the measures from those columns with no calculation applied to the card. ('Show Value as')
You could do it as a single measure but I am going to guess here that you'll want to track historical trends and will want those calculations for a time based usage chart (Weekly/monthly/by Sheet #/etc.)....might as well keep the measures simple and plan for that trend analysis in the future.
You may have just changed the sample data for privacy but I am curious as to how you will work the single sheet # having multiple percentages, especially as they total over 100%.
Trent
TrentS How do I create a measure that can reference those columns? I tried that earlier and it wouldn let me do that. Also, the sheet column is the code for our steel chemistry. I'll use it in a slicer so we can see the utilization of different steels.
Need to use the SUM function in the measure. Essentially putting the calculation in the background as opposed to a setting on the card visual as you encountered.
%_Used = SUM(Sheet1[Total_used]) / SUM(Sheet1[Total_Steel])Once I had created those two columns, I created that as a measure (modeled as a %) for the card value.Then you should be able to use visual level filters to set your cards to the appropriate Month.I understand using that Steel # as a filter. That explains it, thank you. I was thinking of it more as a "Job" or Asset number instead of a classification.