Page/Report level filter and percentage

[SJain]

Hi! So I made this visualisation, which shows sum of some values (say A(green), B(black), C(red)) over some property(which is in axis). 

Now I want to show percentages of B and C where A is the total (i.e. B+C+something). I can do this by group by but the problem is it should be dynamic with filters like date. eg. I want the percentages for last 2 weeks or 4 weeks and would want only those values to be counted. Is there any way this can be done?

[v-qiuyu-msft · Accepted solution]

Hi @SJain,

Accoridng to your descripiton, it seems that you want to calculate the percentage via (B+C)/A, and these percentag values can be dynamic based on differnet values, right?

In your scenairo, if A, B and C comes from the same column field, you can create a calculate table group by the X-axis values (Table1[Group]), then create a measure to calculate the percentage, see:

Table = SUMMARIZE(Table1,Table1[Group],"value",SUMX('Table1',IF(Table1[Category]<>"A",Table1[Amount],0)))

Measure 2 = DIVIDE(SUM('Table'[value]),SUMX('Table1',IF(Table1[Category]="A",Table1[Amount],0)))

Table1Table

check different date range

If I misunderstand your requirement, please correct me. And share some sample data and screenshot about your desired results for our analysis.

If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu

[SJain]

Hi @v-qiuyu-msft, I tried the pbix file you attached, in that slicer if I select all except 2nd and 3rd July, the value of B+C will be 0 for aaa and ideally the measure should be 0, but it is showing 0.5, why is that happening?

[SJain]

Hi @v-qiuyu-msft, thanks for the reply, this help me a lot. I just had one query what happens when we choose something on the slicer on this query,

Measure 2 = DIVIDE(SUM('Table'[value]),SUMX('Table1',IF(Table1[Category]="A",Table1[Amount],0)))

As far as I understand, the new table "Table" is static, so when we click on something on the slicer does the SUM('Table'[value]) also changes? 

[Totte67]

I am not shore,
but what I think you want is that B anc C present % of A.
I think you need to create two new columns that you have B/A and one for C/A.

I hope I under stand you correct.

Best regards
Totte67