Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
4 years ago
Solved

Dax

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 11/01/20211000
client 21/01/20211500
client 31/01/20211300
client 11/02/20211200
client 21/02/20211100
client 31/02/20211450

Table 2

DateEnterpriseSegmentFactor
1/01/2021Client 1residential0.2
1/01/2021Client 1commercial0.3
1/01/2021Client 1industrial0.5
1/01/2021Client 2residential0.35
1/01/2021Client 2commercial0.25
1/01/2021Client 2industrial0.4
1/02/2021Client 1residential0.4
1/02/2021Client 1commercial0.2
1/02/2021Client 1industrial0.4

Expected result table

Dateenterprisesegmentconsumptioncumulative consumption
1/01/2021client 1residential200200
1/02/2021client 1residential480680

Thanks in advance

3 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    4 years ago

    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