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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It 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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.