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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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