Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

How to Compare One Dimension Member to All Others

Hi, 

I would like to crate the formula that could be able to compare one dimension to the others.

For example, I have a list of campaign here. 

Whenever I choose campaign such as: En-more countries search

I could show me the difference of conversion value of En-more countries seach compared to others. 

En-more countries : 5000 

FR-France search: 2000

so the difference between is 3000.

I want it to be show in bar chart like this, instead of Product-subcategory, in my real case it would be : Campaign. 

For more clarification, when I choose Rubber Bands, it show me the difference Profit of Rubber Bands compared to other Sub-Category in the list.

 

this is the model of my data : Campagin[column1]: is the uniquie name of the campaign. I link it with the Campaign with the relationship is : many to one. 

 

Hope to you could help. 

Thanks!! 

Need Help
Show and Tell
  • v-eachen-msft's avatar
    v-eachen-msft
    7 years ago

    Hi Anonymous ,

     

    You can meet your needs by slicer visual and stacked bar chart.

    At first, you need to edit your model. You will need to create a slicer based on table “Campaign”. So you don't need to create any relationship related to table “Campaign” because it will filter your result.

    Here is my test tables and measure “SUM”.


     

     

     

     

     

     

     

     

     

    SUM =
SUM ( sheet1[Column1] )

    You need to create a measure “ComparedResult” .

    ComparedResult =
VAR s =
    SELECTEDVALUE ( Campaign[Column1] )
RETURN
    [SUM]
        - CALCULATE ( SUM ( sheet1[Column1] ), sheet1[Campaign] = s )

    Now you can create a stacked bar visual and slicer visual.

    Best Regards,

    Eads

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-eachen-msft's avatar
    v-eachen-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Anonymous ,

     

    You can meet your needs by slicer visual and stacked bar chart.

    At first, you need to edit your model. You will need to create a slicer based on table “Campaign”. So you don't need to create any relationship related to table “Campaign” because it will filter your result.

    Here is my test tables and measure “SUM”.


     

     

     

     

     

     

     

     

     

    SUM =
SUM ( sheet1[Column1] )

    You need to create a measure “ComparedResult” .

    ComparedResult =
VAR s =
    SELECTEDVALUE ( Campaign[Column1] )
RETURN
    [SUM]
        - CALCULATE ( SUM ( sheet1[Column1] ), sheet1[Campaign] = s )

    Now you can create a stacked bar visual and slicer visual.

    Best Regards,

    Eads

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.