Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Bhatt23
Helper I
Helper I

Grouping for multiple calculation (sum and substract) with respect to categories

‎05-22-2024 03:48 AM

Hi Guys,

Can anyone please help me on this problem.

Here I have one table with the mapping data along with calculation and other one with the data itself.

How can we implement the formula in a single column to group it with Subdivision column and Division.


So the end result should look like the view in end result view below which will group by subdivision and division but the value is as per the calculation mentioned in the calculation sheet tab.

 

Your help or guidance is really appreciated.

 

Thanks!

Formula with mapping view 

DPIDDivisionSubdivisionCalculation based on DPID which represent SubdivisionNames for calculation
9680AAPI 1(9680+9711)-9692-9677API 1
9692AAPI 1  
9677AAPI 1  
9706SBN101  
9711AAPI 1  
9709PBR1  
9689PBR2  
9686PAPA 19686+9674+9710+9675+9670+9678APA 1
9691PAPA 1 Test9691+9672+9687+9671APA 1 Test
9672PAPA 1 Test  
9701NNot known  
9676NNot known  
9710PAPA 1  
9674PAPA 1  
9703AAPI Endurance9702+9688+9703API Endurance
9687PAPA 1 Test  
9678PAPA 1  
9670PAPA 1  
9671PAPA 1 Test  
9702AAPI Endurance  
9688AAPI Endurance  
9675pAPA 1  
9679FCR1  
9697FCR2  
9699SAID_ASC9699+9708AID_ASC
9708SAID_ASC  
9705PAPA 2(9705+9704)-9693-9673APA 2
9693PAPA 2  
9673PAPA 2  
9704PAPA 2  
9694FAPF End Test9694+9700APF End Test
9700FAPF End Test  
9682YYR1  
9696YYR2  
9690SASP & P + Warmtepomp  
9685SASHVAC EB A15  
9683FAPF Labo  
9684SASMT111  
9695SASMT112  
9698SASMT1109684+9695SAS
9707FAPF Labo9683+9707APF Labo
9681MHR1  
9748SMPHR2  
9747SMPHR3  
9753SMPHR4  
9752SMPHR8  
9751SMPHR11  
9750SMPHR12  
9746SMPHR14  
9749SMPHR12  
9693AAPI 29693+9673API 2
9673AAPI 2  

 

Data view 

 

DPIDDivisionSubdivisionVALUE
9680AAPI 112
9692AAPI 113
9677AAPI 115
9706SBN10117
9711AAPI 1200
9709PBR1165.4
9689PBR2203.4
9686PAPA 1241.4
9691PAPA 1 Test279.4
9672PAPA 1 Test317.4
9701NNot known355.4
9676NNot known393.4
9710PAPA 1431.4
9674PAPA 1469.4
9703AAPI Endurance507.4
9687PAPA 1 Test545.4
9678PAPA 1583.4
9670PAPA 1621.4
9671PAPA 1 Test659.4
9702AAPI Endurance697.4
9688AAPI Endurance735.4
9675pAPA 1773.4
9679FCR1811.4
9697FCR2849.4
9699SAID_ASC887.4
9708SAID_ASC925.4
9705PAPA 2963.4
9693PAPA 21001.4
9673PAPA 21039.4
9704PAPA 21077.4
9694FAPF End Test1115.4
9700FAPF End Test1153.4
9682YYR11191.4
9696YYR21229.4
9690SASP & P + Warmtepomp1267.4
9685SASHVAC EB A151305.4
9683FAPF Labo1343.4
9684SASMT1111381.4
9695SASMT1121419.4
9698SASMT1101457.4
9707FAPF Labo1495.4
9681MHR11533.4
9748SMPHR21571.4
9747SMPHR31609.4
9753SMPHR41647.4
9752SMPHR81685.4
9751SMPHR111723.4
9750SMPHR121761.4
9746SMPHR141799.4
9749SMPHR121837.4
9693AAPI 21875.4
9673AAPI 21913.4
9680AAPI 11951.4
9692AAPI 11989.4
9677AAPI 12027.4
9706SBN1012065.4
9711AAPI 12103.4
9709PBR12141.4
9689PBR22179.4
9686PAPA 12217.4
9691PAPA 1 Test2255.4
9672PAPA 1 Test2293.4
9701NNot known2331.4
9676NNot known2369.4
9710PAPA 12407.4
9674PAPA 12445.4
9703AAPI Endurance2483.4
9687PAPA 1 Test2521.4
9678PAPA 12559.4
9670PAPA 12597.4
9671PAPA 1 Test2635.4
9702AAPI Endurance2673.4
9688AAPI Endurance2711.4
9675pAPA 12749.4
9679FCR12787.4
9697FCR22825.4
9699SAID_ASC2863.4
9708SAID_ASC2901.4
9705PAPA 22939.4
9693PAPA 22977.4
9673PAPA 23015.4
9704PAPA 23053.4
9694FAPF End Test3091.4
9700FAPF End Test3129.4
9682YYR13167.4
9696YYR23205.4
9690SASP & P + Warmtepomp3243.4
9685SASHVAC EB A153281.4
9683FAPF Labo3319.4
9684SASMT1113357.4
9695SASMT1123395.4
9698SASMT1103433.4
9707FAPF Labo3471.4
9681MHR13509.4
9748SMPHR23547.4
9747SMPHR33585.4
9753SMPHR43623.4
9752SMPHR83661.4
9751SMPHR113699.4

 

 

End result view 

 

DivisionSubdivisionValue(example not real calculation)[calculation should be with respect to calculation sheet]
AAPI 160
SBN101450
PBR1330
PBR2123
PAPA 1156
PAPA 1 Test183.3
NNot known169.8
AAPI Endurance156.3
FCR1142.8
FCR2129.3
SAID_ASC115.8
PAPA 2102.3
FAPF End Test88.8
YYR175.3
YYR261.8
SASP & P + Warmtepomp48.3
SASHVAC EB A1534.8
FAPF Labo21.3
SASMT1117.8
SASMT112-5.7
SASMT110-19.2
MHR1-32.7
SMPHR2-46.2
SMPHR3-59.7
SMPHR4-73.2
SMPHR8-86.7
SMPHR11-100.2
SMPHR12-113.7
SMPHR14-127.2
Labels:
Message 1 of 16
536 Views
0
1 ACCEPTED SOLUTION
v-xinruzhu-msft
Community Support
Community Support

‎05-22-2024 06:55 PM

Hi @Bhatt23 

Based on your description, if there are no calculation group, will they shold be sum together? such as the BN101.

I create the following sample based on the understanding I have described, you can refer it.

Sample data is the same as you provided.

Create the following measures.

 

Calculation_group =
MAXX (
    FILTER (
        ALLSELECTED ( 'Formula with mapping' ),
        [Division]
            IN VALUES ( 'Data'[Division] )
                && [Subdivision] IN VALUES ( 'Data'[Subdivision] )
    ),
    [Calculation based on DPID which represent Subdivision]
)

Left_calculation = LEFT([Calculation_group],SEARCH(")",[Calculation_group],,BLANK()))
Right_calculation = RIGHT([Calculation_group],LEN([Calculation_group])-SEARCH(")",[Calculation_group],,BLANK()))
Sum_calculation =
VAR a =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Data' ),
            [Division]
                IN VALUES ( 'Data'[Division] )
                    && [Subdivision]
                        IN VALUES ( 'Data'[Subdivision] ) && CONTAINSSTRING ( [Left_calculation], [DPID] )
        ),
        [VALUE]
    )
VAR b =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Data' ),
            [Division]
                IN VALUES ( 'Data'[Division] )
                    && [Subdivision]
                        IN VALUES ( 'Data'[Subdivision] ) && CONTAINSSTRING ( [Right_calculation], [DPID] )
        ),
        [VALUE]
    )
RETURN
    SWITCH (
        TRUE (),
        [Calculation_group] = BLANK (),
            SUMX (
                FILTER (
                    ALLSELECTED ( 'Data' ),
                    [Division]
                        IN VALUES ( 'Data'[Division] )
                            && [Subdivision] IN VALUES ( 'Data'[Subdivision] )
                ),
                [VALUE]
            ),
        [Left_calculation] = BLANK (), b,
        [Left_calculation] <> BLANK (), a - b
    )

 

Output

vxinruzhumsft_0-1716430696099.png

 

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 

View solution in original post

rouping for multiple calculation (sum and substract) with respect to categories.pbix
Message 2 of 16
498 Views
15 REPLIES 15
Bhatt23
Helper I
Helper I

‎05-26-2024 09:06 PM

"group all the data according to calculation on single column"

This is the heading of the new post reagrding my problem, would be great if you can share some thoughts about new post and issues.

I am using summarized new table but still seeing issues with the result.

@v-xinruzhu-msft 

Message 16 of 16
293 Views
0
Bhatt23
Helper I
Helper I

‎05-23-2024 01:03 PM

Hi Yolo,
I tested and it failed.
I forgot to mention few important things:-
The data table consist of Date and time column which is every mins or hours data and the number of rows are more than 200K.
So when I tested this with actual data to see the trend via bar chart it failed with error visual has exceeded the available resources.
Sorry but I should have mentioned these points in the beginning.
So the data table is created after merging the formula with mapping table to data that means data table as separate only consist of DPID, Value and date/time however merging it with Mapping table via DPID gives me the main data table that is as below on which I did calculation and it failed to show me correct result.

 

DPIDDivisionSubdivisionValueDate and time
9680AAPI 116.4932024-04-22T12:15:38.2500000
9692AAPI 116.5082024-04-24T02:38:43.7600000
9677AAPI 116.5122024-04-24T12:38:47.7500000
9706SBN10116.5192024-04-25T07:00:18.2500000
9711AAPI 116.5212024-04-25T12:45:46.7500000
9709PBR116.5252024-04-25T23:38:59.7500000
9689PBR216.5352024-04-27T06:09:10.5400000
9686PAPA 116.542024-04-28T10:39:20.5100000
9691PAPA 1 Test16.5442024-04-29T03:09:26.5100000
9672PAPA 1 Test16.5522024-04-30T00:31:00.2400000
9701NNot known16.5542024-04-30T08:09:38.2300000
9676NNot known16.5572024-04-30T17:39:41.2300000
9710PAPA 116.5682024-05-02T10:39:56.9500000
9674PAPA 116.5742024-05-03T07:15:27.4500000
9703AAPI Endurance16.5782024-05-03T19:40:08.9600000
9687PAPA 1 Test16.5792024-05-04T18:40:16.9700000
9678PAPA 116.582024-05-05T03:15:48.4800000
9670PAPA 116.582024-05-05T09:10:21.9800000
9671PAPA 1 Test16.582024-05-05T12:10:22.9900000
9702AAPI Endurance16.5852024-05-06T12:40:31.7700000
9688AAPI Endurance16.5882024-05-06T21:40:34.7500000
9675pAPA 116.5982024-05-08T03:40:47.1000000
9679FCR116.6032024-05-08T21:00:47.3700000
9697FCR216.6052024-05-09T20:11:02.0100000
9699SAID_ASC16.6092024-05-12T09:41:25.2200000
9708SAID_ASC16.6092024-05-12T13:11:26.2200000
9705PAPA 216.6122024-05-13T09:41:33.2200000
9693PAPA 216.6132024-05-13T12:11:34.2200000
9673PAPA 216.6162024-05-13T20:00:25.4700000
9704PAPA 216.6292024-05-15T09:16:19.4800000
9694FAPF End Test16.6362024-05-16T01:45:30.5000000
9700FAPF End Test16.6432024-05-16T19:42:03.2600000
9682YYR116.6432024-05-16T21:42:04.2500000
9696YYR216.6492024-05-17T10:12:09.2500000
9690SASP & P + Warmtepomp16.65217-05-2024 21:16:15
9685SASHVAC EB A1516.6532024-05-18T16:12:19.2500000
9683FAPF Labo16.6532024-05-18T23:42:22.2500000
9684SASMT11116.6642024-05-22T14:01:13.1400000
9695SASMT1121.53882024-04-30T12:39:40.2300000
9698SASMT1101.53882024-04-30T19:09:42.2300000
9707FAPF Labo1.53882024-04-30T23:39:43.2300000
9681MHR11.5392024-05-02T08:39:56.9500000
9748SMPHR21.5392024-05-02T09:09:56.9500000
9747SMPHR31.53922024-05-02T16:39:58.9500000
9753SMPHR41.53972024-05-04T10:40:13.9600000
9752SMPHR81.53972024-05-04T16:10:15.9700000
9751SMPHR111.53972024-05-05T14:40:23.9800000
9750SMPHR121.53972024-05-05T19:10:25.9800000
9746SMPHR141.53972024-05-06T02:40:28.9900000
9749SMPHR121.54152024-05-09T07:40:57.1300000
9693AAPI 21.54182024-05-12T18:41:28.2200000
9673AAPI 21.5422024-05-13T09:41:33.2200000
9680AAPI 11.5422024-05-13T18:11:36.2200000
9692AAPI 11.54332024-05-17T17:42:11.2500000
9677AAPI 11.54332024-05-18T09:42:17.2500000
9706SBN1011.54332024-05-19T19:42:29.2500000
9711AAPI 11.54332024-05-20T02:42:31.2600000
9709PBR11.543320-05-2024 11:42:35
9689PBR21.54332024-05-20T20:12:38.0100000
9686PAPA 11.54482024-05-22T09:12:52.6400000
9691PAPA 1 Test1.5452024-05-22T21:42:56.6400000
9672PAPA 1 Test3.93312024-04-23T08:08:36.7500000
9701NNot known3.93422024-04-24T08:08:45.7500000
9676NNot known3.93522024-04-25T15:38:56.7500000
9710PAPA 13.93632024-04-26T23:39:08.7700000
9674PAPA 13.93652024-04-27T10:39:12.5100000
9703AAPI Endurance3.93842024-04-30T05:09:37.2300000
9687PAPA 1 Test3.94012024-05-01T23:15:16.9800000
9678PAPA 13.94242024-05-04T11:40:14.9600000
9670PAPA 13.94242024-05-04T22:40:18.9800000
9671PAPA 1 Test3.9442024-05-06T19:40:34.7500000
9702AAPI Endurance3.94462024-05-07T07:40:38.7500000
9688AAPI Endurance3.94632024-05-08T08:45:31.1000000
9675pAPA 13.94662024-05-08T15:10:51.1000000
9679FCR13.9472024-05-11T01:41:12.0100000
9697FCR23.94972024-05-14T00:41:39.2300000
9699SAID_ASC3.95062024-05-14T13:11:43.2300000
9708SAID_ASC3.95142024-05-15T01:41:47.2300000
9705PAPA 23.9542024-05-16T11:00:43.4800000
9693PAPA 23.9552024-05-17T04:42:07.2500000
9673PAPA 23.95562024-05-17T12:42:09.2500000
9704PAPA 23.95592024-05-17T17:12:11.2500000
9694FAPF End Test3.95632024-05-21T09:42:44.6300000
9700FAPF End Test3.95752024-05-22T02:12:50.6400000
9682YYR13.95872024-05-22T18:42:55.6400000
9696YYR239.76623-04-2024 08:31:05
9690SASP & P + Warmtepomp39.76623-04-2024 08:31:05
9685SASHVAC EB A1539.7772024-04-24T07:38:45.7500000
9683FAPF Labo39.7772024-04-24T07:38:45.7500000
9684SASMT11139.7832024-04-24T20:01:16.5000000
9695SASMT11239.7832024-04-24T20:01:16.5000000
9698SASMT11039.7872024-04-25T08:15:25.5000000
9707FAPF Labo39.7872024-04-25T08:15:25.5000000
9681MHR139.79225-04-2024 15:01:06
9748SMPHR239.79225-04-2024 15:01:06
9747SMPHR339.8132024-04-28T03:09:18.5100000
9753SMPHR439.8132024-04-28T03:09:18.5100000
9752SMPHR839.8162024-04-28T13:45:26.7600000
9751SMPHR1139.8162024-04-28T13:45:26.7600000
Message 15 of 16
393 Views
0
v-xinruzhu-msft
Community Support
Community Support

‎05-22-2024 06:55 PM

Hi @Bhatt23 

Based on your description, if there are no calculation group, will they shold be sum together? such as the BN101.

I create the following sample based on the understanding I have described, you can refer it.

Sample data is the same as you provided.

Create the following measures.

 

Calculation_group =
MAXX (
    FILTER (
        ALLSELECTED ( 'Formula with mapping' ),
        [Division]
            IN VALUES ( 'Data'[Division] )
                && [Subdivision] IN VALUES ( 'Data'[Subdivision] )
    ),
    [Calculation based on DPID which represent Subdivision]
)

Left_calculation = LEFT([Calculation_group],SEARCH(")",[Calculation_group],,BLANK()))
Right_calculation = RIGHT([Calculation_group],LEN([Calculation_group])-SEARCH(")",[Calculation_group],,BLANK()))
Sum_calculation =
VAR a =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Data' ),
            [Division]
                IN VALUES ( 'Data'[Division] )
                    && [Subdivision]
                        IN VALUES ( 'Data'[Subdivision] ) && CONTAINSSTRING ( [Left_calculation], [DPID] )
        ),
        [VALUE]
    )
VAR b =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Data' ),
            [Division]
                IN VALUES ( 'Data'[Division] )
                    && [Subdivision]
                        IN VALUES ( 'Data'[Subdivision] ) && CONTAINSSTRING ( [Right_calculation], [DPID] )
        ),
        [VALUE]
    )
RETURN
    SWITCH (
        TRUE (),
        [Calculation_group] = BLANK (),
            SUMX (
                FILTER (
                    ALLSELECTED ( 'Data' ),
                    [Division]
                        IN VALUES ( 'Data'[Division] )
                            && [Subdivision] IN VALUES ( 'Data'[Subdivision] )
                ),
                [VALUE]
            ),
        [Left_calculation] = BLANK (), b,
        [Left_calculation] <> BLANK (), a - b
    )

 

Output

vxinruzhumsft_0-1716430696099.png

 

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 

rouping for multiple calculation (sum and substract) with respect to categories.pbix
Message 2 of 16
499 Views
Bhatt23
Helper I
Helper I
In response to v-xinruzhu-msft

‎05-24-2024 02:31 AM

I will mark this as accepted solution for this scenario and probably will raising a new request for the other realtime data and scenario. 

Message 14 of 16
327 Views
0
Bhatt23
Helper I
Helper I
In response to v-xinruzhu-msft

‎05-22-2024 08:07 PM

Hi Yolo,

Thanks for your response really appreciate for helping me here.

Actually answering to your question 
"Based on your description, if there are no calculation group, will they shold be sum together? such as the BN101"
Well that actually depends like in few cases they will be taken as it is like in BN101 case as it is sum of all BN101.
But in other cases according to different logics subdivision can be sum like YR1 and YR2 which will be sum YR1+YR2.
Also Is there a way we can keep all formulas in a column according to let's say divisions or subdivision and once any row is update in formula or any new division subdivision added than it should take the new ones too.

If require we can connect separately to discuss this please for better understanding of problem.

Thanks,
Bhatt


Message 3 of 16
488 Views
0
v-xinruzhu-msft
Community Support
Community Support
In response to Bhatt23

‎05-22-2024 08:17 PM

Hi @Bhatt23 

Thanks for your quick reply, but on your Formula with mapping view, you don't give the calculation rule like YR1 and YR2, the calculation i offered based on the calculation rule on  Formula with mapping view. and the measures i offered will change if you change the formula,  if you think the solution i offered above, you can consider to mark it as a solution, and based on your new requirement, you can  open a new case.

 

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Message 4 of 16
487 Views
0
Bhatt23
Helper I
Helper I
In response to v-xinruzhu-msft

‎05-22-2024 09:50 PM

Thanks for your response Yolo.
Actually this ask was as per this post only I need to make the 

"Calculation based on DPID which represent Subdivision"

Column in Formula with mapping to be dynamic according to the division and sub division, like if we add a new formula or add a new divsion subdivision row in the table than the calculation should alter accordingly.
It was the original ask according to which your solution is partially correct.

Please advise or assist here so that I can accept this as solution Yolo.

Thanks,
Bhatt

Message 5 of 16
466 Views
0
v-xinruzhu-msft
Community Support
Community Support
In response to Bhatt23

‎05-22-2024 10:05 PM

Hi @Bhatt23 

Thanks for your reply,I still confused that:

1.Based on your description, if you change the formula, the measures will also change dynamically.

2.and do you mean if the calculation rule is blank, so it will also return blank?

3.The new formula , how will it be like? 

Based on my understanding, the solution i offered could achieve it, you coluld test it.

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Message 6 of 16
448 Views
0
Bhatt23
Helper I
Helper I
In response to v-xinruzhu-msft

‎05-22-2024 10:34 PM

Thanks for your response.

1.Based on your description, if you change the formula, the measures will also change dynamically. (That's right thanks for that)

2.and do you mean if the calculation rule is blank, so it will also return blank? (No no)

3.The new formula , how will it be like? (I still need to complete the review of DAX you have provided)

My ask was to have a dynamic column to store formula for all the unique Subdivision as you can see the formulas are scattered in multiple rows in "Calculation based on DPID which represent Subdivision" column, although the formula represents subdivision at end of the day according to the DPID.

Like APA1 = 9686+9674+9710+9675+9670+9678 so no matter how many time the APA1 will repeat in Subdivision column under table "Formula with mapping"
the formula will be same for this.

Message 7 of 16
437 Views
0
v-xinruzhu-msft
Community Support
Community Support
In response to Bhatt23

‎05-22-2024 10:41 PM

Hi @Bhatt23 

Thanks for your reply, based on your description , the measure calculation_group have achieved it, you can drag it to the visual.

vxinruzhumsft_0-1716442870225.png

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Message 8 of 16
435 Views
0
Bhatt23
Helper I
Helper I
In response to v-xinruzhu-msft

‎05-22-2024 11:59 PM

Yeah i checked that and it has some variation incase we select divisions via slicer after making relationship.
Can you please help me understand why we are using Calculation_group measure and than left_calculation and Right_calculation as we are trying to search"(" in calculation_group.

Your support is really appreciated.

Thanks,
Bhatt

Message 9 of 16
429 Views
0
v-xinruzhu-msft
Community Support
Community Support
In response to Bhatt23

‎05-23-2024 12:32 AM

Hi @Bhatt23 

Based on these measures to find the related group, e.g in left_calculation  we can find which dpid should be add, in right_calculation to find which dpid should be substract.

vxinruzhumsft_0-1716449463112.png

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Message 10 of 16
402 Views
Bhatt23
Helper I
Helper I
In response to v-xinruzhu-msft

‎05-23-2024 09:07 PM

Hi Yolo,

Let me know if you want me to create a new post if this doesn't suits.

Message 13 of 16
341 Views
0
Bhatt23
Helper I
Helper I
In response to v-xinruzhu-msft

‎05-23-2024 08:34 PM

Hi Yolo,

Once the query is resolved than only i will mark as accepted this as solution but as mentioned i missed to put few things in beginning but when I tried with actual data the logics failed.
Can you please advise and I will implement and accept this as solution.

Message 12 of 16
371 Views
0
Bhatt23
Helper I
Helper I
In response to v-xinruzhu-msft

‎05-23-2024 08:21 PM

Hi Yolo,
I tested and it failed.
I forgot to mention few important things:-
The data table consist of Date and time column which is every mins or hours data and the number of rows are more than 200K.
So when I tested this with actual data to see the trend via bar chart it failed with error visual has exceeded the available resources.
Sorry but I should have mentioned these points in the beginning.
So the data table is created after merging the formula with mapping table to data that means data table as separate only consist of DPID, Value and date/time however merging it with Mapping table via DPID gives me the main data table that is as below on which I did calculation and it failed to show me correct result.

 

DPIDDivisionSubdivisionValueDate and time
9680AAPI 116.4932024-04-22T12:15:38.2500000
9692AAPI 116.5082024-04-24T02:38:43.7600000
9677AAPI 116.5122024-04-24T12:38:47.7500000
9706SBN10116.5192024-04-25T07:00:18.2500000
9711AAPI 116.5212024-04-25T12:45:46.7500000
9709PBR116.5252024-04-25T23:38:59.7500000
9689PBR216.5352024-04-27T06:09:10.5400000
9686PAPA 116.542024-04-28T10:39:20.5100000
9691PAPA 1 Test16.5442024-04-29T03:09:26.5100000
9672PAPA 1 Test16.5522024-04-30T00:31:00.2400000
9701NNot known16.5542024-04-30T08:09:38.2300000
9676NNot known16.5572024-04-30T17:39:41.2300000
9710PAPA 116.5682024-05-02T10:39:56.9500000
9674PAPA 116.5742024-05-03T07:15:27.4500000
9703AAPI Endurance16.5782024-05-03T19:40:08.9600000
9687PAPA 1 Test16.5792024-05-04T18:40:16.9700000
9678PAPA 116.582024-05-05T03:15:48.4800000
9670PAPA 116.582024-05-05T09:10:21.9800000
9671PAPA 1 Test16.582024-05-05T12:10:22.9900000
9702AAPI Endurance16.5852024-05-06T12:40:31.7700000
9688AAPI Endurance16.5882024-05-06T21:40:34.7500000
9675pAPA 116.5982024-05-08T03:40:47.1000000
9679FCR116.6032024-05-08T21:00:47.3700000
9697FCR216.6052024-05-09T20:11:02.0100000
9699SAID_ASC16.6092024-05-12T09:41:25.2200000
9708SAID_ASC16.6092024-05-12T13:11:26.2200000
9705PAPA 216.6122024-05-13T09:41:33.2200000
9693PAPA 216.6132024-05-13T12:11:34.2200000
9673PAPA 216.6162024-05-13T20:00:25.4700000
9704PAPA 216.6292024-05-15T09:16:19.4800000
9694FAPF End Test16.6362024-05-16T01:45:30.5000000
9700FAPF End Test16.6432024-05-16T19:42:03.2600000
9682YYR116.6432024-05-16T21:42:04.2500000
9696YYR216.6492024-05-17T10:12:09.2500000
9690SASP & P + Warmtepomp16.65217-05-2024 21:16:15
9685SASHVAC EB A1516.6532024-05-18T16:12:19.2500000
9683FAPF Labo16.6532024-05-18T23:42:22.2500000
9684SASMT11116.6642024-05-22T14:01:13.1400000
9695SASMT1121.53882024-04-30T12:39:40.2300000
9698SASMT1101.53882024-04-30T19:09:42.2300000
9707FAPF Labo1.53882024-04-30T23:39:43.2300000
9681MHR11.5392024-05-02T08:39:56.9500000
9748SMPHR21.5392024-05-02T09:09:56.9500000
9747SMPHR31.53922024-05-02T16:39:58.9500000
9753SMPHR41.53972024-05-04T10:40:13.9600000
9752SMPHR81.53972024-05-04T16:10:15.9700000
9751SMPHR111.53972024-05-05T14:40:23.9800000
9750SMPHR121.53972024-05-05T19:10:25.9800000
9746SMPHR141.53972024-05-06T02:40:28.9900000
9749SMPHR121.54152024-05-09T07:40:57.1300000
9693AAPI 21.54182024-05-12T18:41:28.2200000
9673AAPI 21.5422024-05-13T09:41:33.2200000
9680AAPI 11.5422024-05-13T18:11:36.2200000
9692AAPI 11.54332024-05-17T17:42:11.2500000
9677AAPI 11.54332024-05-18T09:42:17.2500000
9706SBN1011.54332024-05-19T19:42:29.2500000
9711AAPI 11.54332024-05-20T02:42:31.2600000
9709PBR11.543320-05-2024 11:42:35
9689PBR21.54332024-05-20T20:12:38.0100000
9686PAPA 11.54482024-05-22T09:12:52.6400000
9691PAPA 1 Test1.5452024-05-22T21:42:56.6400000
9672PAPA 1 Test3.93312024-04-23T08:08:36.7500000
9701NNot known3.93422024-04-24T08:08:45.7500000
9676NNot known3.93522024-04-25T15:38:56.7500000
9710PAPA 13.93632024-04-26T23:39:08.7700000
9674PAPA 13.93652024-04-27T10:39:12.5100000
9703AAPI Endurance3.93842024-04-30T05:09:37.2300000
9687PAPA 1 Test3.94012024-05-01T23:15:16.9800000
9678PAPA 13.94242024-05-04T11:40:14.9600000
9670PAPA 13.94242024-05-04T22:40:18.9800000
9671PAPA 1 Test3.9442024-05-06T19:40:34.7500000
9702AAPI Endurance3.94462024-05-07T07:40:38.7500000
9688AAPI Endurance3.94632024-05-08T08:45:31.1000000
9675pAPA 13.94662024-05-08T15:10:51.1000000
9679FCR13.9472024-05-11T01:41:12.0100000
9697FCR23.94972024-05-14T00:41:39.2300000
9699SAID_ASC3.95062024-05-14T13:11:43.2300000
9708SAID_ASC3.95142024-05-15T01:41:47.2300000
9705PAPA 23.9542024-05-16T11:00:43.4800000
9693PAPA 23.9552024-05-17T04:42:07.2500000
9673PAPA 23.95562024-05-17T12:42:09.2500000
9704PAPA 23.95592024-05-17T17:12:11.2500000
9694FAPF End Test3.95632024-05-21T09:42:44.6300000
9700FAPF End Test3.95752024-05-22T02:12:50.6400000
9682YYR13.95872024-05-22T18:42:55.6400000
9696YYR239.76623-04-2024 08:31:05
9690SASP & P + Warmtepomp39.76623-04-2024 08:31:05
9685SASHVAC EB A1539.7772024-04-24T07:38:45.7500000
9683FAPF Labo39.7772024-04-24T07:38:45.7500000
9684SASMT11139.7832024-04-24T20:01:16.5000000
9695SASMT11239.7832024-04-24T20:01:16.5000000
9698SASMT11039.7872024-04-25T08:15:25.5000000
9707FAPF Labo39.7872024-04-25T08:15:25.5000000
9681MHR139.79225-04-2024 15:01:06
9748SMPHR239.79225-04-2024 15:01:06
9747SMPHR339.8132024-04-28T03:09:18.5100000
9753SMPHR439.8132024-04-28T03:09:18.5100000
9752SMPHR839.8162024-04-28T13:45:26.7600000
9751SMPHR1139.8162024-04-28T13:45:26.7600000
Message 11 of 16
374 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

View All