Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I need help to compute a Column called Total which is a measure. I have 2 slicer Country and Date . if Country is "India" alone . I hve 2 conditions
1) Sum the Loan by Comp and also country
2) Sum the Balance column by Comp but excluding the slicer Country if its India alone. I have attached the Input data and expected result showing how to arrive.
Challenge is SUM Balance for All the country If India is Selected . I am also using Dimension table for Country for Slicer
Fact Table
Country | Comp | Date | Loan | Balance |
India | Accen | 7/31/2023 | 10 | |
USA | Microsoft | 7/31/2023 | 5 | |
Japan | Apple | 7/31/2023 | 0 | 2 |
China | 7/31/2023 | 0 | 2 | |
India | Microsoft | 7/31/2023 | 2 | |
USA | Microsoft | 7/31/2023 | 5 | |
India | Apple | 7/31/2023 | 0 | 5 |
China | Accen | 7/31/2023 | 5 | |
China | Accen | 7/31/2023 | 5 |
Expected Result :
Solved! Go to Solution.
Hi @swasim ,
You can create the measures as below to get it, please find the details in the attachment.
Measure =
VAR _selcountry =
SELECTEDVALUE ( 'Countries'[Country] )
VAR _comps =
CALCULATETABLE (
VALUES ( 'Table'[Comp] ),
FILTER ( 'Table', 'Table'[Country] = _selcountry )
)
VAR _loan =
CALCULATE (
SUM ( 'Table'[Loan] ),
FILTER ( 'Table', 'Table'[Country] = _selcountry )
)
VAR _balance =
CALCULATE (
SUM ( 'Table'[Balance] ),
FILTER (
'Table',
IF ( _selcountry = "India", 1 = 1, 'Table'[Country] = _selcountry )
&& 'Table'[Comp] IN _comps
)
)
RETURN
_loan + _balance
Sum of Loan = SUMX(VALUES('Table'[Comp]),[Measure])
Best Regards
Hi @swasim ,
You can create the measures as below to get it, please find the details in the attachment.
Measure =
VAR _selcountry =
SELECTEDVALUE ( 'Countries'[Country] )
VAR _comps =
CALCULATETABLE (
VALUES ( 'Table'[Comp] ),
FILTER ( 'Table', 'Table'[Country] = _selcountry )
)
VAR _loan =
CALCULATE (
SUM ( 'Table'[Loan] ),
FILTER ( 'Table', 'Table'[Country] = _selcountry )
)
VAR _balance =
CALCULATE (
SUM ( 'Table'[Balance] ),
FILTER (
'Table',
IF ( _selcountry = "India", 1 = 1, 'Table'[Country] = _selcountry )
&& 'Table'[Comp] IN _comps
)
)
RETURN
_loan + _balance
Sum of Loan = SUMX(VALUES('Table'[Comp]),[Measure])
Best Regards