Calculating the total of a the values obtained from a measure

[niel_orvyn1]

Hi there

I need to give the total of spesific "Tiers" in a dashboard. 

If managed to get an output measure with an if statement looking at the values in the 'TPS' column that can either be done by an measure or (count of total events within that specific minute). However, I now need the output values "IFTPS measure" calculated so I can either give individual total of each Tier. This will give me an indication of how many events where ">=X" over a period. 

It would be great if possible, that the value also changes according to the slider. 

Attached image migh give a better description: 

 

Would be great if anyone could help. 

Best Regards,

Niel

[amitchandak]

@niel_orvyn1 , Can you share the current calculation.  Based on that only count need to be calculated

[niel_orvyn1]

@amitchandak , thanks for replying. Im very new to all if this, I can share the Desktop file with you or? Is there any other way to give you more information. 

Mant thanks,

Niel

[amitchandak]

You can share the file or the formula to have look

[niel_orvyn1]

Hi

Thank you. Please see the calculations for the 3 columns. 

1. Fetchtime calculation: Consists of taking the Date and Time column into 3 separate columns. At the end by just joining them again, for some when moving away from the data hierarchy it did not want to summary per timestamp

2. TPS calculation: TPS = sum(Data[Counter])

3.IFTPS measure =

IF(AND(Data[TPS]>=1,Data[TPS]<2),">=1TPS | <2TPS",

IF(AND(Data[TPS]>=2,Data[TPS]<3),">=2TPS | <3TPS",

IF(AND(Data[TPS]>=2,Data[TPS]<3),">=2TPS | <3TPS",

IF(AND(Data[TPS]>=3,Data[TPS]<4),">=3TPS | <4TPS",

IF(AND(Data[TPS]>=4,Data[TPS]<5),">=4TPS | <5TPS",

IF(AND(Data[TPS]>=5,Data[TPS]<6),">=5TPS | <6TPS",

IF(AND(Data[TPS]>=6,Data[TPS]<30),">=6TPS | < and above"

)))))

))

Thanks in advance. 

Regards,

Niel

[v-diye-msft]

Hi @niel_orvyn1 

Let me know if you'd like to get below one:

Column = CALCULATE(COUNTROWS('Table'),FILTER('Table',[Column1]=EARLIER('Table'[IFTPS measure])))

If not, you might consider creating pbix file that will contain some sample data (remove the confidential info), upload the pbix to onedrive for business and share the link to the file. Please do not forget to describe the expected results based on this sample data.

[niel_orvyn1]

Hi @v-diye-msft 

Let me know if you can access this link: 

https://orvyncom-my.sharepoint.com/:u:/g/personal/niel_orvyn_com/EdZ2J-9ZcjxGlthIBv0h1PcBUhjL34oYD05...

The results I would like to achieve: 

Im trying to do demand planning/capacity planning. At the end of the excersize I would like the dashboard to show from the data provided over the last 3months, what capacity is required of tokens fetched per second and per minute. So if I have a for example a 2token capacity, how many above 2 will be dropped, so then I will have to maybe provide for 5, or 6 etc tokens in order to avoid feched from dropping. 

1. Have a look at the query. I merged to Tables (Combine and TransactionsSQL). Majority of the information contained in the Combine Table. After merging the two table I did some cleaning and created a duplicate table, I renamed the duplicate, Data. 

2. The data containes a timestamp and I am interested in the events (in this cases token fetched for an API) during certian time stamps in mili seconds and seconds. You will see I have changed Date and time to Fetch time, I had to go throug a work around in order for PowerBi to give me values per time. (not sure if it is best practice). 

3. The visual "TPS Jan-Mar 2020" summarized that (if you look at the top row) on/in 10/02/2020 10:26:47 a total of 28 token fetches occured during that second. 

4. I then thought it best to create a list of IF statements in order to group the data so I can start calculating:

• Tiers:/Groups for example (Qty and % where tokens > 2 or 3 or 5 etc per second) (doesnt have to be text can be 1-2; 2-4 etc)
• Frequency of occurence 
• Moving averages
• Spikes and the duration between spikes

It is fairly easy to do with excel pivots but I want to exceed in powerbi. Interesting I cannot find many exmpales in this regard, and the online certification did not even touch this topic. 

Would be very grateful for a solution. I've been at it for 7 days and not moving forward.

Best Regards,

Niel

[niel_orvyn1]

Hi

This article pretty much put me in the right direction: https://www.red-gate.com/simple-talk/sql/bi/using-the-filter-function-in-dax/

A big thank you to Andy Brown on the red-gate site👍