Power BI (DAX) Multiple Filter Sum Total

0
votes

I am a new user of Power BI. I have a two step process which I need help with.

(1) - Calculate filtered (string) sum totals, which update based on slicers in report

(2) - Calculate ratio using outputs from Step 1 by quarter.

Data set approximates this:

enter image description here

The idea is that my report should be able to calculate Price per Liter if filtered by Color and if filtered by Shade (ie. multiple filters, but not restricted), by Quarter, so the trend can be plotted.

So far, I can calculate what I need using only one filter.

Total Price per quarter = 
CALCULATE (
    SUM ( Data[Price] ),
    FILTER ( data, data[Quarter] = EARLIER ( data[quarter] ) ),
    FILTER ( data, data[Colour] = EARLIER ( data[Colour] ) )
)

The trouble is that this approach doesn't seem to be so accurate when including more than one filter. From what I can gather it seems to double count Price (if say using two filters)

Total Price (TP) should essentially adjust for each filter (Quarter, Colour & Shade). If Q1 Selected TP = 2800 (1000+500+600+700); If Q1 & Green, TP = 1500 (1000+500); If Q1, Green & Light, TP = 1000. I know it is a simple problem, but I am not quite familiar with the functions and syntax just yet.

Can you please provide me with a DAX expression which might achieve this?

1
powerbidax
Welcome to StackOverflow, Kasrel! What have you done so far yourself? – RADO
Hey RADO. So far, I can calculate what I need using only one filter. – Kasrel
It's a good idea to move all that information to your question. – RADO
Very well, I have edited the question to include the additional information. – Kasrel

1 Answers

1
votes

Just use the following measure without any filter (right click on your table and add measure):

Total Price = SUM(data[Price])

Now put that measure into a KPI visual and put the rest of your columns as slicers on the report. You will see if you select for example Q1 the KPI visuals value will change. The same goes for every filter combination you will choose.