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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Illusionsquid
Regular Visitor

Measure displays the same value for all month

I have a measure that calculates the average KPI using YTD data (the number will be updated along with the dataset update monthly). I need the calculated number to remain unchanged for all months rather than being calculated for each month. Dax that I'm using (Orange line):

YTD1 =
  var CMKCXCritics =
    CALCULATE(
        COUNTROWS('KCX database'),
        FILTER(
            'KCX database','KCX database'[Score group CM]="Critics"))
  var CMKCXSupporter =
    CALCULATE(
        COUNTROWS('KCX database'),
        FILTER(
            'KCX database','KCX database'[Score group CM]="Supporter"))
  var CMKCXFans =
    CALCULATE(
        COUNTROWS('KCX database'),
        FILTER(
            'KCX database','KCX database'[Score group CM]="Fans"))
  var CMTotal = CMKCXCritics + CMKCXSupporter + CMKCXFans
  var CMKCX = ((CMKCXFans - CMKCXCritics) / CMTotal) * 100

  var CCKCXCritics =
    CALCULATE(
        COUNTROWS('KCX database'),
        FILTER(
            'KCX database','KCX database'[Score group CC]="Critics"))
  var CCKCXSupporter =
    CALCULATE(
        COUNTROWS('KCX database'),
        FILTER(
            'KCX database','KCX database'[Score group CC]="Supporter"))
  var CCKCXFans =
    CALCULATE(
        COUNTROWS('KCX database'),
        FILTER(
            'KCX database','KCX database'[Score group CC]="Fans"))
  var CCTotal = CCKCXCritics + CCKCXSupporter + CCKCXFans
  var CCKCX = ((CCKCXFans - CCKCXCritics) / CCTotal) * 100

return
  (CMKCX + CCKCX)/2

Illusionsquid_0-1698746144224.png

(The number changes every month)

When I add  && 'KCX database'[Month] >= "1" && 'KCX database'[Month] <= "12" in FILTER, it shows the correct number but for Jan only.

Illusionsquid_1-1698746171751.png

How do I make it only calculate the overall number instead of each month and show the same number for all months?

Thank you all!!!

1 ACCEPTED SOLUTION
Illusionsquid
Regular Visitor

I figured it out!!!

For those who need it, follow as below because it worked with me.

Step 1: Create a new dataset using "Enter Data" function. You will then add a month column from 1-12.
Step 2: Go to Model View, and create a relationship with the month column using your new dataset and your original dataset.
Step 3: Add a new column to your new dataset using "New Column" function. You will then write (or copy) your measure into this column.
Step 4: Drag your measure to the chart, change the type to MAX (it is usually set as count) and woala!!! 

Illusionsquid_0-1698756022406.png

From my understanding, when you do it this way, the system will understand that your measure is run individually for each month instead of run by YTD for every month.

Hope it works for you too!!!



View solution in original post

1 REPLY 1
Illusionsquid
Regular Visitor

I figured it out!!!

For those who need it, follow as below because it worked with me.

Step 1: Create a new dataset using "Enter Data" function. You will then add a month column from 1-12.
Step 2: Go to Model View, and create a relationship with the month column using your new dataset and your original dataset.
Step 3: Add a new column to your new dataset using "New Column" function. You will then write (or copy) your measure into this column.
Step 4: Drag your measure to the chart, change the type to MAX (it is usually set as count) and woala!!! 

Illusionsquid_0-1698756022406.png

From my understanding, when you do it this way, the system will understand that your measure is run individually for each month instead of run by YTD for every month.

Hope it works for you too!!!



Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel1

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.