Sum of calculated column is wrong

[Martin_Hris]

I hope that you will understand this as I have written it back and forth and got a little bit lost.

I am trying to accomplish a simple task. Calculate the KPI's, how many Ci's per agent and technology we have in our support. I have list of CI's with solution specified, I need to count them, match them to a technology role, ci role and multiply the count of CI's by specified factor based on the ci role. Then I have a list of agents and each has a technology role. So just divide count of CI's multiplied by factor divided by count of agents supporting the technology.

I have 2 tables. One is containing configuration item data with various solutions (windows, unix etc.) I have 2nd table where I have list of all unique possible solutions, each is then mapped to it's own technology and based on technology the last column is normalization factor, which means that each CI is multiplied by this normalization factor.

Data looks like this

System name Solution name and some other columns with info about CI's.

dummy1         windows

dummy2         linux

dummy3         unix

2nd table is

Solution name      technology role              technology ci      normalization factor

windows               technology:windows       windows             1

linux                     technology:Linux/unix     linux                    1

unix                      technologyLinux./unix     unix                     2

I need simple thing. Count all distinct windows CI's and multiply by norm factor 1. Then do the same for all the solution, for Unix it's count of CI's * 2.

I thought that it would be easier to populate the norm factor to each server in 1st table, by creating calculated column

Column = RELATED(normf[Normalization factor])

This worked and each row in 1st table contains correct norm factor.

But when I try to create a new table and use sum of norm factor the results are like this

Technology Role	Technology CI	Count of System Name	Normalization factor	Column
		18248
Technology: Backup	backup	14271	1	38017
Technology: Database	Database - db2	32	1	114
Technology: Database	Database - Microsoft SQL Server	1960	1	7768
Technology: Database	Database - Oracle	1403	1	8968
Technology: Database	Database - Other DBMS	44	2	382
Technology: Database	Oracle high availability & Storage instances​	27	1.19	96.39
Technology: SAP		1333
Technology: Storage	NAS Storage	61	1	184
Technology: Storage	Other storage	55	1	158
Technology: Storage	SAN storage	794	1	2388
Technology: Storage	SAN switch	599	0.26	463.06
Technology: Unix/Linux		506
Technology: Unix/Linux	Linux	11316	1	33960
Technology: Unix/Linux	Unix	896	1.24	3224
Technology: Windows		1126
Technology: Windows	Hyper-V Host​	105	2.25	708.75
Technology: Windows	VMWare Host​	2621	2.25	18029.25
Technology: Windows	Windows	23068	1	68854

[v-yadongf-msft]

Hi @Martin_Hris ,

I am so glad to hear that your problem has been solved . Please consider Accept it as the solution to help the other members find it more quickly.

Best regards,

Yadong Fang

[Greg_Deckler]

@Martin_Hris When you say "create a new table and the results are like this" are you referring to a table in the data model, table visual or ?

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Martin_Hris · Accepted solution]

After some more searching i think that I have  found a solution for the calculation

Distinct.normf.CI.count = [Distinct.CI.count]*MIN('Delivery Contract CI List'[norm.factor])

Where Distinct.CI.count is a measure and Delivery Contract CI List'[norm.factor] is a column. I found on the community forums that to have this multiplication working I have to use MIN, MAX for the column.

Now the numbers of CIs are calculated correctly I just need to go through this article, to fix the measeure totals.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[Martin_Hris]

Thanks, I've spent quite a lot of time searching throught the articles. But righ now I finally understand where is the problem. I have a list of System names which are not unique and number o recurrences is based on how many activities we are supporting for the system.

Each of the system has a normalization factor assigned in its row using new custom column

Normf = RELATED(normf[Normalization factor])

Then I need to display distinct count of system names and sum of the normalization factors. But the sum of normalization factors calculated in not for distincs system names but it calculates the sum for all of them.

What I am trying to achieve look very simple. Do dictinct count of system names sorted by CI Technology and multiply this count by the normalization factor for this CI technology.