Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Grand total columns - using dynamic measures (selecting within slicer)

Hi Power BI community,   since several days I struggle with the problem of calculating total values per each column. I have created dynamicaly selected measures using slicer. The problem relates to...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Anonymous ,

    Actually, total level is based on value fields, you can't use measure to split column fields on total level. You can consider to move one column field to row level and use row level total to instead:

    dyn_measure = 
SWITCH(
    SELECTEDVALUE(dyn_values[ID]),
    1, FORMAT([V1], "#,##0"),
    2, FORMAT([V2], "#,##0"),
    FORMAT([V1]+[V2], "#,##0")
)

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Anonymous ,

 

Please share a pbix file with some sample data to help us clarify your data structure and test to coding formula on it.

Notice: do mask on sensitive data.

 

Regards,

Xiaoxin Sheng

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Anonymous,

     

    I attach the pbix file with the current problem I struggle with.

     

    Link: PBIX report

     

    Kind regards,

    Nicr

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Anonymous 

       

      Any updates?

       

      Kind regards,

      Nicr

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi Anonymous ,

        Actually, total level is based on value fields, you can't use measure to split column fields on total level. You can consider to move one column field to row level and use row level total to instead:

        dyn_measure = 
SWITCH(
    SELECTEDVALUE(dyn_values[ID]),
    1, FORMAT([V1], "#,##0"),
    2, FORMAT([V2], "#,##0"),
    FORMAT([V1]+[V2], "#,##0")
)

        Regards,

        Xiaoxin Sheng