Search code examples
powerbidaxdata-analysispowerbi-desktopmeasure

Dynamically Generate Target in Matrix Using DAX

I have a Matrix visual in Power BI where I'm attempting to count various values by categories and sum them up as you +/- through:

enter image description here

The issue comes where there is no value within that date range, so for example on 3/03/2024:

enter image description here

Note how it totals up to 18, and not 24. That is because the 0 count actually consists of no data/rows for that particular date.

My Target measure is as follows:

 Target = 

  var target = 6

  var working = SUMX(SUMMARIZE(TableName,AllVisits[Person]),target)

  return if (working = BLANK(), target, working)

What do I need to change in the measure to have the Target be counted all the way up the tree structure?

Here's a sample pbix file: https://file.io/sBwzI0uiApXo

Solution

  • Can you try the following measure:

    Target = 
    var people = 
    CALCULATETABLE(
        SUMMARIZE(AllVisits,AllVisits[Hub], AllVisits[Team Leader], AllVisits[Housing Officer]), 
        REMOVEFILTERS(DimDate)
    )
    var t = GENERATE(people, VALUES(DimDate[WeekStartDate]))
    return  SUMX(t, 6)