Measure checking conditions in one table on 2 granularities

[Leesanity]

Hi team,

I have a use case as follow:

 1) User selected 2 SPs (e.g. SP7 and SP8)
 2) The measure will fetch the selected SPs (Achieve via Selectedvalue()) and do follow logic check:

    1) Total Qty of 2 SPs:

               a.if they are the same, then go to step 2)

               b.if they are different, then count as "Total different" 
    2) Qty distribution under each RDD: if total qty are the same,

               a. the qty distribution are the same (SP7 vs SP8), then count as "Total and distribution are the same"

               b. the qty distribution are different (SP8 vs SP9), then count as "Total are the same but distribution are different"

The raw data is as follow:

Key	RDD	SP	Qty
1	202503	SP7	2000
1	202502	SP7	300
1	202503	SP8	2000
1	202502	SP8	300
1	202412	SP9	2000
1	202412	SP9	300
1	202502	SP8	0
1	202412	SP9	0

In order to better help you understand, I made a visual in excel:

If we selected SP7 and SP8, then 
 1) SP7 total is 2300 and SP8 total is 2300 so total are the same.

 2) SP7 has 300 in 202502 and 2000 in 202503. SP8 has the same distribution. 

-> we will get "Total and distribution are the same"

If we selected SP8 and SP9, then 

1) SP8 total is 2300 and SP9 total is 2300 so total are the same. 

 2) SP8 has 300 in 202502 and 2000 in 202503 while SP9 has all 2300 assigned under 202412. 

-> we will get "Total are the same but distribution are different"

Please let me know if I state the question clear. Otherwise I will add more info.

Cheers,

Tian

[rajendraongole1 · Accepted solution]

Hi @Leesanity - Yes, your explanation is clear, and your use case provides a logical flow for calculating and comparing total quantities and distribution across selected SPs. 

Identify Selected SPs: Use the SELECTEDVALUE function to capture the two SPs selected by the user. If no SPs or fewer than two are selected, return a default value or handle appropriately.

SelectedSP1 = SELECTEDVALUE(Table[SP], "No SP Selected")
SelectedSP2 = SELECTEDVALUE(Table[SP], "No SP Selected")

Calculate Total Quantity for Each SP: Use a measure to calculate the total quantity for each SP.

TotalQty_SP1 = CALCULATE(SUM(Table[Qty]), Table[SP] = [SelectedSP1])
TotalQty_SP2 = CALCULATE(SUM(Table[Qty]), Table[SP] = [SelectedSP2])

Check If Totals Are the Same: Create a measure to compare the totals for the two SPs.

TotalSame = IF([TotalQty_SP1] = [TotalQty_SP2], TRUE(), FALSE())

Compare Quantity Distribution by RDD: Use a measure to evaluate if the quantity distribution by RDD for the two SPs is identical.

DistributionSame =
IF(
COUNTROWS(
FILTER(
SUMMARIZE(
Table,
Table[RDD],
"Qty_SP1", CALCULATE(SUM(Table[Qty]), Table[SP] = [SelectedSP1]),
"Qty_SP2", CALCULATE(SUM(Table[Qty]), Table[SP] = [SelectedSP2])
),
[Qty_SP1] <> [Qty_SP2]
)
) = 0,
TRUE(),
FALSE()
)

Create the Final Output Logic: Based on the above measures, create a final measure to classify the result.

Result =
SWITCH(
TRUE(),
NOT [TotalSame], "Total Different",
[DistributionSame], "Total and distribution are the same",
"Total are the same but distribution are different"
)

Ensure your slicer limits the selection to exactly two SPs.For larger datasets, optimize the DAX queries to avoid performance issues.Consider creating calculated tables for intermediate steps, Hope this helps . 

[Leesanity]

Hi @rajendraongole1 ,

Thanks a lot for your solution and help!

Cheers,

Tian