Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
So I know that this topic has been covered in hundred threads, and I probably have read them all, but still cant make it work.
I have this data set
https://drive.google.com/file/d/1lah5UpgkTd1umJAyPDGmPd7HHujH_a6h/view?usp=sharing
There are two table connected with bridge tables. I have created a measure that summarizes ValueB for 11 rolling days.
Measure = IF(SUM(Table2[ValueA])>0;
CALCULATE( SUM(Table1[ValueB]); Table2[Type]="TypeX"; FILTER(ALL(DateTable); DateTable[Date]>=MAX(DateTable[Date])-5 && DateTable[Date]<=MAX(DateTable[Date])+5)); BLANK())
What I need is Percentage of this Measure for this set. 127 to become 0,1591478696741855, 143 - 0,1791979949874687, etc.
Hi @xxenoss ,
You may create measure like DAX below.
Percentage =
var d=CALCULATE(SUM(Table2[ValueA]),FILTER(ALLSELECTED(Table1), Table1[Date]=MAX(Table1[Date])&&Table1[LocationA]=MAX(Table1[LocationA])&&Table1[LocationB]=MAX(Table1[LocationB])))
return
IF(d<>BLANK(),DIVIDE([Measure], d),0)
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Unfortunately that didn’t work for me.
Let me simplify the problem.
Table1 contains Date, LacationA, LocationB, Duration, ValueB
Table2 contains Date, Type, LocationB, Duration, ValueA
My final table looks like this. Columns Date, LocationB and Duration, are from Bridge tables that connects Table1 & Table2.
And ValueA is function ValueA=IF(SUM(Table1[ValueB])>0, SUM(Table2[ValueA]), BLANK())
Lets say I need just Sum of ValueA for this setup. Tipically I would do it like this
Measure = IF(SUM(Table1[ValueB])>0, CALCULATE([ValueA], ALLSELECTED(Duration[Duration])), BLANK())
The problem with it is since there is no LocationA column in Table2, it summarizes ValueA for all the Durations available for specific Date & LocationB in Table2. As you can see in the screenshot result is 856, instead of 728, Cause in Table2 there are additional Durations, that aren’t present in Table1
So I need to filter Durations to those that are available in Table1 for specific Date, LocationA and LocationB.
I tried to do it like this, but it doesn’t work
Measure:=IF([Sum of ValueB]>0,
CALCULATE([ValueA], ALLSELECTED(Duration[Duration]), (Table1[LocationA])), BLANK())
Hi @xxenoss ,
The percentages you are calculating are based on what values? Can you share how they are calculated?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCalculation is as follows
Measure as it is shown in the example devided by sum of Measure For specific Date, LocatoinA, LocationB, but not Duration.
For example in the screenshot in will be 127/798, 143/798, 131/798. Basically I need to find Sum of Measure and the devide Mesure by its SUM.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |