Dax

[Syndicate_Admin]

Good Community I have a query I am trying to make a measure in which I can obtain the accumulated energy by segment until a given date, for which I have to multiply the energy consumption by client and its respective factor, so far I have managed to obtain the results of each client by segment but at the time of trying to generate the cumulative has generated problems
the measure I use to generate energy consumption by factor is as follows (Energy by factor = Sum (table1 [Energy Consumption]) * Max (table2 [factor)) and I think here is the problem, there is some other way to find the enegia by segment in this case I use MAX to locate the factor, but at the time of making the accumulated data do not fit me because of the function that always touches the maximum factor

Table 1

Enterprise	Date	Energy Consumption
client 1	1/01/2021	1000
client 2	1/01/2021	1500
client 3	1/01/2021	1300
client 1	1/02/2021	1200
client 2	1/02/2021	1100
client 3	1/02/2021	1450

Table 2

Date	Enterprise	Segment	Factor
1/01/2021	Client 1	residential	0.2
1/01/2021	Client 1	commercial	0.3
1/01/2021	Client 1	industrial	0.5
1/01/2021	Client 2	residential	0.35
1/01/2021	Client 2	commercial	0.25
1/01/2021	Client 2	industrial	0.4
1/02/2021	Client 1	residential	0.4
1/02/2021	Client 1	commercial	0.2
1/02/2021	Client 1	industrial	0.4

Expected result table

Date	enterprise	segment	consumption	cumulative consumption
1/01/2021	client 1	residential	200	200
1/02/2021	client 1	residential	480	680

Thanks in advance

[CNENFRNL · Accepted solution]

[TomMartens]

Hey @Syndicate_Admin ,

please take the time and create a Power BI Desktop file that contains sample data but still reflects your data model by using the manual input method in Power BI desktop. Upload the file to onedrive or dropbox and share the link. If you are using Excel to create the sample data share the xlsx as well.

I do not understand the rule to multiply the enegy consumption of client1 on the 1st of January 2021 as there are three factors in table that have client 1, the same data, namely the 1st of January 2021. From the expected result, it seems the factor of residential has been used to calcualte the energy consumption. This is the rule that I do not understand.

On the 2nd of January the energy consumption is 480 how has this been calculated and what is the rule behind this calculation.

Regards,

Tom

[Syndicate_Admin]

Hello Thank you very much for answering in effect to calculate the energy consumption by segment of a customer you have to have the total of the energy consumed in a month, after this total you have to multiply by the factor of the segment of that Month to obtain the amount per segment of that customer

https://drive.google.com/drive/folders/1h0doE4AFUqXs3BpVmqSP43e9wot1T7wq?usp=sharing

attached the link where the test pbi file is equantered I hope you can enter thanks in advance