Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AnthFromIngram
Frequent Visitor

Need help with a measure and conditional statements for how to calculate it

Need to build a custom measure to say count or sum the total Sales[Net Sales] x 12 unless subscription[plan name] contains text (NCE COM ANN) if contains text (NCE COM ANN) then count it but do not multiply it's value by 12.

 

Essentially the issue im trying to solve is, i'm trying to get the total annual revenue from the column Sales[Net Sales] but I only want to multiply the monthly subscriptions by 12. 

 

I'm not sure what the correct syntax for this would be, and its tough to find any info to help piece this together myself.

 

There's also a slicer on the page for relative date, I would need the net sales total to reflect the total for the selected timeframe. 

 

I'm working with a direct query so creating columns or tables isn't an option as it'll turn it into a composite model and won't allow for scheduled refresh anymore. Has to be a measure.

 

Anything helps! Thank you!

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

Hi @AnthFromIngram ,

 

If you want to show results group by month, I suggest you to create a dimdate and then create a relationship between it with your data table.

DimDate =
ADDCOLUMNS (
    CALENDARAUTO (),
    "Year", YEAR ( [date] ),
    "Month", MONTH ( [date] )
)

Measure:

Filter =
VAR _Net_Sales1 =
    CALCULATE (
        SUM ( Sales[Net Sales] ),
        FILTER ( subscription, subscription[plan name] <> "NCE COM ANN" )
    )
VAR _Net_Sales2 =
    CALCULATE (
        SUM ( Sales[Net Sales] ),
        FILTER ( subscription, subscription[plan name] = "NCE COM ANN" )
    )
RETURN
    _Net_Sales1 * 12 + _Net_Sales2

If this reply still couldn't help you solve your issue, please share a sample file with me and show me the result you want.

 

Best Regards,
Rico Zhou

 

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

2 REPLIES 2
AnthFromIngram
Frequent Visitor

Thank you so much! this worked great, I didnt need to setup a date table because there was already one in the datamodel so the date slicer i had on each page works fine.

v-rzhou-msft
Community Support
Community Support

Hi @AnthFromIngram ,

 

If you want to show results group by month, I suggest you to create a dimdate and then create a relationship between it with your data table.

DimDate =
ADDCOLUMNS (
    CALENDARAUTO (),
    "Year", YEAR ( [date] ),
    "Month", MONTH ( [date] )
)

Measure:

Filter =
VAR _Net_Sales1 =
    CALCULATE (
        SUM ( Sales[Net Sales] ),
        FILTER ( subscription, subscription[plan name] <> "NCE COM ANN" )
    )
VAR _Net_Sales2 =
    CALCULATE (
        SUM ( Sales[Net Sales] ),
        FILTER ( subscription, subscription[plan name] = "NCE COM ANN" )
    )
RETURN
    _Net_Sales1 * 12 + _Net_Sales2

If this reply still couldn't help you solve your issue, please share a sample file with me and show me the result you want.

 

Best Regards,
Rico Zhou

 

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.