Forum Discussion

elcapitano's avatar
elcapitano
Regular Visitor
4 years ago
Solved

Aggregating fields but applying total at a specific dimension

Hi folks,

 

I am hoping this is a simple question and its a bit of a funky problem to solve that I've not come across in Power BI. Basically if you imagine we have a dataset which produces Field column (with values A, B, C) and Measure 1 and we want to do something where a new measure (Measure 2) is an aggregate of the everything in Measure 1 but when we display it we only want it to sit at the field A and to ignore B and C

 

Is this sort of thing possible?

 

Field Measure 1Measure 2
A1017
B5 
C2 

 

 

Need Help
  • mahoneypat's avatar
    mahoneypat
    4 years ago

    You can try an expression like this.

     

    Measure2 =
    VAR M1sumx =
    SUMX ( ALLSELECTED ( Table[Field] ), [Measure 1] )
    RETURN
    IF ( MIN ( Table[Field] ) = "A", M1sumx )

     

    Pat

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    4 years ago

    You can try an expression like this.

     

    Measure2 =
    VAR M1sumx =
    SUMX ( ALLSELECTED ( Table[Field] ), [Measure 1] )
    RETURN
    IF ( MIN ( Table[Field] ) = "A", M1sumx )

     

    Pat

  • vojtechsima's avatar
    vojtechsima
    Icon for Super User rankSuper User
    4 years ago

    Hi, elcapitano,
    I am not sure it's the best approach to create a calculated column for this, I would probably just create a measure to specific visual, however, if you want to have a physical table like you showed, you can use something like this:

    Measure 2 = 
var summarizeEverything = sum('Table'[Measure 1])
var FieldCheck = "A"

var CheckCondition = if('Table'[Field] = FieldCheck, summarizeEverything)

return CheckCondition

     

    If you want a more specific formula, please share the actual dataset.

  • elcapitano's avatar
    elcapitano
    Regular Visitor
    4 years ago

    Thanks for the suggestions - both great. But I think I'll need to elaborate on the situtation a bit more as this piece works for a simple problem but I have something that's a bit more complicated so will create a follow up problem for it. Will create a PBIX file for it too