Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi
Need help with below requirement
https://drive.google.com/file/d/1EtWTRa6KNSekliI2pDYoyxqrCZwjr8Oq/view?usp=drive_link
I have a table with actuals for total volume, own volume, net revenue. The requirement is for total volume, we have to consider KPI "Total Volume" and for Own volume we have to consider, "Own Volume" kpi but for Australia even for Own Volume we need to take Total volume of australia. For "NR/HL", logic is divide(NR/Own Volume).
Could you please help me with the dax formula to get total volume for Australia in Own volume row
Total Volume= Total Volume of all countries
Own Volume= Own Volume of all countries but for Australia consider Total Volume
Net Revenue= Net revenue of all countries
NR/HL= divide(NR,Own Volume)
I also need to see total.
Thanks in advance
Solved! Go to Solution.
Hi @Anonymous ,
I updated your sample pbix file(see the attachment), please check if that is what you want.
1. Create a measure as below
Measure =
VAR sel =
SELECTEDVALUE ( 'KPI'[KPI] )
VAR selcountry =
SELECTEDVALUE ( 'Data'[Country] )
VAR volume =
DIVIDE ( CALCULATE ( SUM ( Data[AC] ), Data[KPI] = "Total Volume" ), 1000, 0 )
VAR ownvol1 =
DIVIDE ( CALCULATE ( SUM ( Data[AC] ), Data[KPI] = "Own Volume" ), 1000, 0 )
VAR ownvol =
IF (
SELECTEDVALUE ( 'Data'[Country] ) = "Australia",
DIVIDE (
CALCULATE (
SUM ( Data[AC] ),
FILTER ( ALLSELECTED ( Data ), Data[KPI] = "Own Volume" )
),
1000,
0
),
ownvol1
)
VAR nr =
DIVIDE ( CALCULATE ( SUM ( Data[AC] ), Data[KPI] = "NET REVENUE" ), 1000, 0 )
VAR nrhl =
DIVIDE ( nr, ownvol, 0 ) * 1000
VAR result =
IF (
sel = "Total Volume",
volume,
IF (
sel = "Own Volume",
ownvol,
IF (
sel = "Net Revenue",
nr,
IF ( sel = "NR/HL", nrhl, volume + ownvol + nr + nrhl )
)
)
)
RETURN
result
2. Update the formula of measure [Actuals] as below
Actuals =
IF (
SELECTEDVALUE ( 'Data'[Country] ) = "Australia",
[Measure],
SUMX ( VALUES ( 'Data'[Country] ), SUMX ( VALUES ( 'KPI'[KPI] ), [Measure] ) )
)
Best Regards
Hi @Anonymous ,
It seems that I don't have the sufficient privilege to access your shared file. Could you please grant me the proper permission to access it? And you can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
https://drive.google.com/file/d/1EtWTRa6KNSekliI2pDYoyxqrCZwjr8Oq/view?usp=sharing
I have updated the permissions...Could you please check now
Hi @Anonymous ,
I updated your sample pbix file(see the attachment), please check if that is what you want.
1. Create a measure as below
Measure =
VAR sel =
SELECTEDVALUE ( 'KPI'[KPI] )
VAR selcountry =
SELECTEDVALUE ( 'Data'[Country] )
VAR volume =
DIVIDE ( CALCULATE ( SUM ( Data[AC] ), Data[KPI] = "Total Volume" ), 1000, 0 )
VAR ownvol1 =
DIVIDE ( CALCULATE ( SUM ( Data[AC] ), Data[KPI] = "Own Volume" ), 1000, 0 )
VAR ownvol =
IF (
SELECTEDVALUE ( 'Data'[Country] ) = "Australia",
DIVIDE (
CALCULATE (
SUM ( Data[AC] ),
FILTER ( ALLSELECTED ( Data ), Data[KPI] = "Own Volume" )
),
1000,
0
),
ownvol1
)
VAR nr =
DIVIDE ( CALCULATE ( SUM ( Data[AC] ), Data[KPI] = "NET REVENUE" ), 1000, 0 )
VAR nrhl =
DIVIDE ( nr, ownvol, 0 ) * 1000
VAR result =
IF (
sel = "Total Volume",
volume,
IF (
sel = "Own Volume",
ownvol,
IF (
sel = "Net Revenue",
nr,
IF ( sel = "NR/HL", nrhl, volume + ownvol + nr + nrhl )
)
)
)
RETURN
result
2. Update the formula of measure [Actuals] as below
Actuals =
IF (
SELECTEDVALUE ( 'Data'[Country] ) = "Australia",
[Measure],
SUMX ( VALUES ( 'Data'[Country] ), SUMX ( VALUES ( 'KPI'[KPI] ), [Measure] ) )
)
Best Regards
User | Count |
---|---|
70 | |
46 | |
21 | |
21 | |
15 |
User | Count |
---|---|
124 | |
42 | |
39 | |
28 | |
24 |