Adding up a total value for isolated measures per category

[Anonymous]

Hi All,

I'm trying to achieve a total value for isolated measures.

For example.

Client A has a equal share of 11,11% and a value of 72 , his expected value is 8,00.

Client B has a equal share of 9,09% and a value of 480, his expectecd value is 43,64.

Client and Equal share belongs to Tabel 1.

Value belongs to Tabel 2. 

Key between Tables is one to many Tabel1[AccountID] 1 -> * Tabel2[AccountID].

The meausure we have created is the following:

Measure 1 = Sum(Tabel 1[Equal Share]) * Sum(Tabel 2[Value]).

In a visual, on account level, it works fine. But when we want to know the total the outcome for this measure, the measure gets it wrong. I was expecting a value of 250 (adding up all the expected values for the different clients) but we get a much higer number. I think it is adding up all the different equal shares and multiply this by the total values. 

Someone has an idea of the change I have to make to the measure?

Many Thanks!

Sample of my data:

Expected outcome

Client A has 3 records in Tabel 2. Thus expected outcome for client A is 11,11% * 3 (count of records) = 0,3333.

Client B has 4 records in Tabel 2. Thus expected outcome for client B is 9,09% * 4 (count of records) = 0,3636

Client C has 3 records in Tabel 2. Thus expected outcome for client C is 9,09% * 3 (count of records) = 0,2727

Now I want to sum up the individual results, this will give me 0,9696. But he following measure will give me a other result namely:

SUMX (
    'Tabel 1',
    'Tabel 1'[Equal Share] * CALCULATE ( SUM ( 'Tabel 2'[Value] ) )
)

 29,29% (sum of all the percentages in Tabel 1) * 10 (sum of all the record counts in Tabel 2) = 2,929 instead of 0,9696. 

[AlB]

Hi @Anonymous

You should show a sample of your tables (in tabular/text format) and an example, based on your data, of what you get and what you need to get. Include where/how you are using the measure. Is it on a matrix visual? With what fields? If you can share the pbix much better. Think of people reading the question and make it easier for them to understand.

I'm not sure I've got it but try this for your measure:

Measure =
SUMX (
    'Tabel 1',
    'Tabel 1'[Equal Share] * CALCULATE ( SUM ( 'Tabel 2'[Value] ) )
)

[Anonymous]

Hi @AlB,

You are totaly right, I will change the opening post.

Greetings,

Ronald

[AlB · Accepted solution]

Hi @Anonymous

Thanks for the update. It looks waaaay better now. Describing your issue like this will get a solution very quickly. Well done.

The only missing thing is that you should also show your data also on tabular/text format, like the following. This way the people trying to answer can readily copy the data to run some tests if they need to:

Account      Equal Share

A	0,1111
B	0,0909
C	0,0909

Going back to the question. If everything is set as you describe, the measure I provided earlier and that you show now in the initial post returns 58,58% at the total and not 2,929 as you claim.

Anyway, since you've now clarified you want the COUNT instead of SUM, try this which will yield the 96,96% at the total:

Measure_v2 =
SUMX (
    'Tabel 1',
    'Tabel 1'[Equal Share] * CALCULATE ( COUNT ( 'Tabel 2'[Value] ) )
)

 Cheers

Code formatted with