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
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.
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
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.
Hi @Bhatt23
Thanks for your reply, based on your description , the measure calculation_group have achieved it, you can drag it to the visual.
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
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.
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.
